Regarding aggr function

qliksus — Thu, 08 Oct 2009 12:37:21 GMT

Hi,

I have a excel file from which contain budegt of the year. Here i want to calculate the latest budget yearwise. means i have a Type dimension which contains value 1,2,3,4 respectively. Means 1 is first fuxed budget, 2 is the second budget and like this. Suppose for a period for all this type amount is present means i have to take amount type 4 because this is the latest budget fixed. I am able to get latest budget in a pivot chart by taking period as a dimension but yearwise i can't able to get the whle sum value. because i wanto use the whole sum amount yearwise in gauge Chart.

My excel data is

Type	Year	PERIOD	Amount
1	2009	01	2,139
1	2009	02	1,822
1	2009	03	1,753
1	2009	04	1,973
1	2009	05	1,966
1	2009	06	1,937
1	2009	07	2,108
1	2009	08	1,937
1	2009	09	1,880
1	2009	10	1,920
1	2009	11	1,930
1	2009	12	2,101
2	2009	01	2,092
2	2009	02	1,753
2	2009	03	2,010
2	2009	04	1,923
2	2009	05	2,369
2	2009	06	1,892
2	2009	07	1,947
2	2009	08	1,872
2	2009	09	1,880
2	2009	10	1,879
2	2009	11	1,892
2	2009	12	1,917
3	2009	7	2,055
3	2009	8	2,110
3	2009	9	2,165
3	2009	10	2,220
3	2009	11	2,275
3	2009	12	2,330
4	2009	10	2,350
4	2009	11	2,380
4	2009	12	2,400
1	2010	1	2100
1	2010	2	2,200
1	2010	3	2300
1	2010	4	2,400
1	2010	5	2500
1	2010	6	2,600
2	2010	4	2200
2	2010	5	2,250
2	2010	6	2300

Now i am able to see correct output while taking the dimensions like period and year in a pivot chart and output is

Type	Year	PERIOD	Amount
2	2009	01	2,092
2	2009	02	1,753
2	2009	03	2,010
2	2009	04	1,923
2	2009	05	2,369
2	2009	06	1,892
3	2009	7	2,055
3	2009	8	2,110
3	2009	9	2,165
4	2009	10	2,350
4	2009	11	2,380
4	2009	12	2,400
1	2010	1	2100
1	2010	2	2,200
1	2010	3	2300
2	2010	4	2200
2	2010	5	2,250
2	2010	6	2300

but i want the output like

Year	Amount
2009	25499
2010	13350

so that i can get amount in text object also. I know this can be done by using aagr function. But unfortunately i tried but still unable to get the output.

can anyone help me?????????

Regarding aggr function

Thu, 08 Oct 2009 15:36:00 GMT

Hi,

I hope the attached example helps.

Good luck!

Rainer

Regarding aggr function

qliksus — Fri, 09 Oct 2009 12:55:28 GMT

Hi,

Thanks for ur reply. But unfornately this is not my requirement. In your file ur data is like for a one period there is only one Type. Actually scenario is little bit different like for a period there should many type no i.e period(1) contains Type(1,2,3 etc ). At that time u have calculate the amount of the maximum Type Year wise.

Regarding aggr function

Fri, 09 Oct 2009 13:17:28 GMT

Hi,

than you have tomofify your Group By Statement in a way like this:

AggregatedBudget:
Load
Type as AggrType,
Year,
Sum(Amount) as AmountByYear
Resident Budget
Group By Year, Type
;

Hope it helps.

Rainer

Hope it helps.

Rainer

topic Regarding aggr function in QlikView

Regarding aggr function

Regarding aggr function

Regarding aggr function

Regarding aggr function