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: 
tyember1
Contributor III
Contributor III

Creating combo chart with two measure calculated by dimension field

Hello Qlik Community,

I have been trying to create a bar chart showing a sum based on the year found in the date field on the record (a 2017 vs 2018 comparison using the same date field and same qty field) my date format is YYYY-MM-DD. My data begins Jan 1st, 2017.

The chart expression I am using is IF(LEFT(End_Date,4) < '2018', sum(Aggr(Sum(Qty),End_Date)))

The expression checker validates it, but It still will not produce any visualization. I have tried loading the date in different format styles but no joy. Is there a different way to use the aggr function to display the measure based on a dimension value? Or should I be loading the date data differently?

Thanks for the help.

1 Solution

Accepted Solutions
Digvijay_Singh

May be something like this -

Dimension - Valuelist('2017','2018')

Measure - Pick(Match(Valuelist('2017','2018'),'2017',2018'),

Sum(If(LEFT(Date,4) = '2017',Qty)),

Sum(If(LEFT(Date,4) = '2018',Qty))

View solution in original post

4 Replies
sunny_talwar

May be you need this

Sum(Aggr(Sum({<Date = {"$(='<=' & Date(MakeDate(2018), 'YYYY-MM-DD'))"}>} Qty), Date))

Digvijay_Singh

May be something like this -

Dimension - Valuelist('2017','2018')

Measure - Pick(Match(Valuelist('2017','2018'),'2017',2018'),

Sum(If(LEFT(Date,4) = '2017',Qty)),

Sum(If(LEFT(Date,4) = '2018',Qty))

tyember1
Contributor III
Contributor III
Author

I realize now I should not have called my dimension "Date", I updated it to "End_Date" so I could tell the difference between what needs to be the date function and what needs to be my dimension.

tyember1
Contributor III
Contributor III
Author

Thank you! I completely overlooked putting Sum before the IF function.. that solved everything. Thanks again for your help!