Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kushalthakral
Creator III
Creator III

Create Summary Report and use formula on individual level

Hi All

i have a following requirement like i have a table below

ModelIDModel NameBasic Price1Basic Price2

Consider Price

(if BasicPrice2<BasicPrice1 then BasicePrice2 else BasicPrice1)

1

A101510
2A202520
3B302525
4B202020
5B151414
6A101010
7A131717
8B101010

And then i need to create summary report like below

Model NameConsider Price
A57
B69

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

1 Solution

Accepted Solutions
Anonymous
Not applicable

PFA

,

Hope it helps

Regards

Nitin

View solution in original post

8 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try Aggr() like this

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

Regards,

Jagan.

kushalthakral
Creator III
Creator III
Author

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

Anonymous
Not applicable

PFA

,

Hope it helps

Regards

Nitin

kushalthakral
Creator III
Creator III
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.

kushalthakral
Creator III
Creator III
Author

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

jagan
Luminary Alumni
Luminary Alumni

Hi,

Please find attached file for solution.

Regards,

Jagan.

kushalthakral
Creator III
Creator III
Author

Thanks Jagan

For the help

Regards

Kushal