Values differ for a measure when used with Case statement

[Former Member]

Hi All,

In IDT, when I run a measure against a dimension, it returns me a value in ##,###.## format. Eg. (12,345.99). However the same measure when used with Case statement it returns me complete number i.e 12346. This happens for all dimension values which affect total.

Please see below resultset for better idea. Can somebody tell me if there is a way to get values in fractions through Case statement?

ID	Measure	case Measure
A123	-223127	-223127
A124	5405.93	5406
A125	5082.25	5082
A126	180305.9	180306
A127	-1348	-1348
A128	-576.31	-576
A129	121708.9	121709
A130	69.71	70
A131	224	224
A132	317.97	318
A133	4729388	4729388
A134	4994.88	4995
A135	5501.99	5502
A136	82657.42	82657
A137	68697.39	68697
A138	46039.09	46039
A139	6.43	6
A140	-300996	-300996
A141	18582.21	18582
A142	1234.8	1235
A143	1578968	1578968
A144	6744.48	6744
A145	5228.54	5229
A146	883.4	883
A147	116676.3	116676
A148	151277.8	151278
A149	-118665	-118665
A150	3875.03	3875
A151	39238.1	39238
A152	15842.71	15843
A153	-204.19	-204
A154	49248.7	49249
A155	3232.11	3232
A156	-384.12	-384
A157	-43.99	-44
A158	30605.89	30606
A159	294.84	295
A160	5687.53	5688
A161	20973.5	20974
A162	10334.2	10334
A163	-166.8	-167
A164	2259.55	2260
A165	16809.03	16809
A166	34321.17	34321
A167	245468.2	245468
A168	10590.83	10591
A169	55935.57	55936
A170	126.89	127
A171	43055.64	43056
A172	39266.27	39266
A173	-47930.3	-47930
A174	-469.53	-470
A175	-122.7	-123
A176	37156.6	37157
A177	31412.23	31412
A178	-143.31	-143
A179	40639.08	40639
A180	6722.54	6723
A181	95034.86	95035
A182	-425.41	-425
A183	515446.9	515447
A184	1639249	1639249
A185	7461.85	7462
A186	7037.57	7038
A187	122439.7	122440
A188	3838919	3838919
A189	-8025.3	-8025
A190	-17038.9	-17039
A191	62809.75	62810
A192	14158.95	14159
A193	8.73	9
A194	4923.14	4923
A195	988.73	989
A196	20857.14	20857
A197	-807.69	-808
A198	67033.86	67034
A199	2770143	2770143
A200	-137.48	-137
A201	410797.6	410798
A202	8445.87	8446
A203	175871.6	175872
A204	51368.85	51369
A205	-113850	-113850
A206	-38148.7	-38149
A207	7502919	7502919
A208	-15859.3	-15859
A209	50388.05	50388
A210	0	0
A211	23137.92	23138
A212	104427.6	104428
A213	2255.54	2256
A214	19439.73	19440
A215	13386103	13386103
A216	987.48	987
A217	-588.51	-589
A218	150401.8	150402
A219	-169.64	-170
A220	10864.84	10865
A221	14358.75	14359
A222	145011	145011
A223	11954463	11954463
A224	31064.96	31065
A225	1053.98	1054
A226	-2259.82	-2260
A227	-112438	-112438
A228	-49173	-49173
A229	-796.54	-797
A230	-24293.7	-24294
A231	66733.96	66734
A232	17950.1	17950
A233	-0.01	0
A234	-3095.11	-3095
A235	260273.1	260273
A236	-18.29	-18
A237	294531.6	294532
A238	-3758.96	-3759
A239	24328.06	24328
A240	2171.89	2172
A241	919590.4	919590
A242	72890.74	72891
A243	64774.84	64775
A244	-108.5	-109
A245	7381457	7381457
A246	2301165	2301165
A247	111484.6	111485
A248	709571.3	709571
A249	0	0
A250	72200.07	72200
A251	19832.97	19833
A252	28161.96	28162
A253	-57490	-57490
A254	99.81	100
A255	74858.91	74859
A256	10033.64	10034
A257	-45.43	-45
A258	0	0
A259	73785.5	73786
A260	-732.46	-732
A261	136341.9	136342
A262	432737.5	432737
A263	-280.79	-281
Total :-	63014221	63014227

As you can see there is difference of 6 in total.

Regards,

Tejas

[Former Member]

Try this at webi Level

Use If Else Condition for case Requirement

tonumber(FormatNumber([Measure];"####.##"))

and then select the Cell >> format number and add a Custom format 0.00 Or ypu can use Other one which are available.

Hope this will help

[former_member225163]

Hi,

Pls try abs() function or any date formatting function to get ##,###.## (specific to the databse) on top the Case statement and test. I tried to reproduce the same. .but, could not. So, this is just a try.

Thanks,

Bala

[former_member189638]

Did you try defining the Custom Format for the Case Measure Object in the IDT??

[Former Member]

I did but that did not help.

With one or two values, there is no issue. Rounding up almost 141 values leads to make a difference of 6 as showing in my first post.