Grouping data (Not Field) with formula

[shamsu_mk]

Hello

I have some data s example mentioned below table

Ac          Amount          BC

1               6000               CA

2               3000               CL

3               2000               CA

4               4000               CL

I need to calculate "CR" with the following formula

CR= sum( CA) / Sum(CL)

Thanks

Shamsu

[sunny_talwar · Accepted solution]

May be this

Sum({<BC = {'CA'}>}Amount)/Sum({<BC = {'CL'}>}Amount)

[shamsu_mk]

Thank you, it worked properly

[shamsu_mk]

Hi

I need an additional support on the above query

I have a QlikView report as mentioned below.The column "change"  shows the cumulative variance.

For example Change in CA, The value for May was 68 and June is 69. so the variance is 1

The script used calculate this as If(RowNo(TOTAL) = 1, 0, RangeSum([CA] - Above(TOTAL [CA])))    

Since the script depends on the Rows I can not apply this expression in the chart.

I need a chart to show the change in Ca and Change in CL. Please help me.

Year	Month	CA	Change in CA	Var. %	CL	Change in CL	Var. %
2014	Jan	72	0	-	42	0	-
2014	Feb	67	-6	-8%	36	-7	-16%
2014	Mar	63	-3	-5%	43	8	22%
2014	Apr	62	-2	-2%	45	2	4%
2014	May	68	6	10%	55	10	23%
2014	Jun	69	1	2%	57	2	3%
2014	Jul	65	-4	-5%	56	-2	-3%
2014	Aug	58	-8	-12%	51	-5	-9%
2014	Sep	54	-3	-6%	46	-5	-10%
2014	Oct	56	2	3%	45	0	0%
2014	Nov	58	2	4%	49	3	8%
2014	Dec	89	31	54%	31	-18	-37%

Thanks

Shamsu

[sunny_talwar]

Would you be able to share a qvw sample to show the issue?

[shamsu_mk]

Hi

Yes, I will send.