Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
SR2
Contributor III
Contributor III

SUM TOTAL with if condition

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

 

 

 

Labels (1)
3 Replies
MayilVahanan

Hi 

May be try like below

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
SR2
Contributor III
Contributor III
Author

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

 

SR2
Contributor III
Contributor III
Author

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