topic Re: SUM TOTAL with if condition in QlikView

SUM TOTAL with if condition

SR2 — Wed, 03 Mar 2021 10:13:18 GMT

Hello Experts,

I want to create 100% stacked bar chart and using TOTAL keyword in expression.

Dimensions -

1) End of month (last 5 months) - This is reporting date i.e. [EOM_Date]

2) Break up of Sale across different Segments i.e. [Segment]

Measure -

sum(if ([EventDate] <= EOM_Date,[Sale Amt ]))

How to TOTAL keyword in above expression to get break up of Segment -

sum(if ([EventDate] <= EOM_Date, total <[Segment]>[Sale Amt ])) gives error as expected )

Appreciate your help!

PS - I can not remove if condition in the sum and need to know how to use sum, if and total together to get stacked bar chart.

Thanks,

Sumeer

Re: SUM TOTAL with if condition

MayilVahanan — Wed, 03 Mar 2021 10:17:20 GMT

May be try like below

sum(total <[Segment]> if ([EventDate] <= EOM_Date, [Sale Amt ])

Re: SUM TOTAL with if condition

SR2 — Wed, 03 Mar 2021 12:11:08 GMT

Hi MayilVahanan,

Thank you for the quick reply.

Sorry, I had some network issues so it took longer for me to check and respond here.

The expression is correct syntactically but it is not creating bucket within the given month rather than across all months. Meaning, instead of giving total sale amt for say a month it is calculating total across all months. This is because the dimension Segment is spread across all months.

It seems the total keyword is not useful to create 100% stacked bar chart in this case as the dimension is spread across all months. I am able to achieve it by creating each measure separately though.

Thanks,

Sumeer

Re: SUM TOTAL with if condition

SR2 — Wed, 03 Mar 2021 21:19:01 GMT

Hi All,

Just to update that I am able to achieve this using total as below -

sum(if([Event Date] <= EOM_Date,[Sale Amt]))
/
sum(total<EOM_Date> if([Event Date] <= EOM_Date,[Sale Amt]))

I was incorrectly using <Segment> in total which was giving skewed figures but the above works perfectly.

Thanks,

Sumeer