topic Re: Creating combo chart with two measure calculated by dimension field in App Development

Creating combo chart with two measure calculated by dimension field

tyember1 — Fri, 06 Apr 2018 18:02:50 GMT

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.

Re: Creating combo chart with two measure calculated by dimension field

sunny_talwar — Fri, 06 Apr 2018 18:28:34 GMT

May be you need this

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

Re: Creating combo chart with two measure calculated by dimension field

Digvijay_Singh — Fri, 06 Apr 2018 18:29:04 GMT

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))

Re: Creating combo chart with two measure calculated by dimension field

tyember1 — Fri, 06 Apr 2018 18:49:27 GMT

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.

Re: Creating combo chart with two measure calculated by dimension field

tyember1 — Fri, 06 Apr 2018 19:12:36 GMT

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