topic Re: Create Summary Report and use formula on individual level in QlikView

Create Summary Report and use formula on individual level

kushalthakral — Mon, 21 Apr 2014 06:18:50 GMT

Hi All

i have a following requirement like i have a table below

ModelID	Model Name	Basic Price1	Basic Price2	Consider Price (if BasicPrice2<BasicPrice1 then BasicePrice2 else BasicPrice1)
1	A	10	15	10
2	A	20	25	20
3	B	30	25	25
4	B	20	20	20
5	B	15	14	14
6	A	10	10	10
7	A	13	17	17
8	B	10	10	10

And then i need to create summary report like below

Model Name	Consider Price
A	57
B	69

But when i am applying formula it is considering total values like

For A

BasicPrice1 53 BasicPrice2 67 and Consider Price 53

For B

BasicPrice1 75 BasicPrice2 109 and Consider Price 75

Which is wrong

and Desired output is in table

Please Help

Thanks

Kushal Thakral

Re: Create Summary Report and use formula on individual level

jagan — Mon, 21 Apr 2014 06:45:41 GMT

Hi,

Try Aggr() like this

Sum((Aggr(If(BasicPrice2 < BasicPrice1, BasicPrice2, BasicPrice1), ModelName, ModelID))

Regards,

Jagan.

Re: Create Summary Report and use formula on individual level

kushalthakral — Mon, 21 Apr 2014 06:59:59 GMT

Hi Jagan

Thanks for the Reply

But if i need to use aggr function then i also need to add Model ID as Dimension in summary report, which i dont waana show in summary report

Re: Create Summary Report and use formula on individual level

Anonymous — Mon, 21 Apr 2014 07:01:25 GMT

PFA

Hope it helps

Regards

Nitin

Re: Create Summary Report and use formula on individual level

kushalthakral — Mon, 21 Apr 2014 07:10:57 GMT

Thanks Nitin

It works

But i was looking to derive it in expression like Jagan told me

But for the same i have to add Material in the report which i dont waana show.

Warm Regards

Kushal

Re: Create Summary Report and use formula on individual level

jagan — Mon, 21 Apr 2014 07:11:32 GMT

Hi Kushal,

You don't need to add ModelID in the summary report. Aggr() will do the calculation at given level without the need to give in Dimension.

Sum((Aggr(If(BasicPrice2 < BasicPrice1, BasicPrice2, BasicPrice1), ModelName, ModelID))

Regards,

Jagan.

Re: Create Summary Report and use formula on individual level

kushalthakral — Mon, 21 Apr 2014 07:15:07 GMT

But i had tried it and i m getting null value in the output

and when i include the Model ID it is showing correct values

Re: Re: Create Summary Report and use formula on individual level

jagan — Mon, 21 Apr 2014 07:43:59 GMT

Hi,

Please find attached file for solution.

Regards,

Jagan.

Re: Create Summary Report and use formula on individual level

kushalthakral — Mon, 21 Apr 2014 08:10:47 GMT

Thanks Jagan

For the help

Regards

Kushal

ModelID	Model Name	Basic Price1	Basic Price2	Consider Price (if BasicPrice2<BasicPrice1 then BasicePrice2 else BasicPrice1)
1	A	10	15	10
2	A	20	25	20
3	B	30	25	25
4	B	20	20	20
5	B	15	14	14
6	A	10	10	10
7	A	13	17	17
8	B	10	10	10

ModelID	Model Name	Basic Price1	Basic Price2	Consider Price (if BasicPrice2<BasicPrice1 then BasicePrice2 else BasicPrice1)
1	A	10	15	10
2	A	20	25	20
3	B	30	25	25
4	B	20	20	20
5	B	15	14	14
6	A	10	10	10
7	A	13	17	17
8	B	10	10	10

ModelID	Model Name	Basic Price1	Basic Price2	Consider Price (if BasicPrice2<BasicPrice1 then BasicePrice2 else BasicPrice1)
1	A	10	15	10
2	A	20	25	20
3	B	30	25	25
4	B	20	20	20
5	B	15	14	14
6	A	10	10	10
7	A	13	17	17
8	B	10	10	10