Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Try Aggr() like this
Sum((Aggr(If(BasicPrice2 < BasicPrice1, BasicPrice2, BasicPrice1), ModelName, ModelID))
Regards,
Jagan.
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
PFA
,
Hope it helps
Regards
Nitin
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
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.
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
Hi,
Please find attached file for solution.
Regards,
Jagan.
Thanks Jagan
For the help
Regards
Kushal