13 Month Chart Assistance

[lshorey]

Hello, I've combed through the forums and haven't been able to find a suitable answer for this.  

I have created a billing dashboard. For my data visualization, I have created a combo chart. 

• Dimension is my Invoice Date.  I have made my Invoice Date just the Month. 
• Measure 1 is Invoice Amount for the last 13 months (in this example, July 2022 - July 2023) , the expression used is: =Sum({<INVOICEDATE = {">=$(=Date(AddMonths(Max(INVOICEDATE ),-12),'YYYY-MM-DD'))<=$(=Max(INVOICEDATE ))"}>} INVOICEAMT)
• Measure 2 is Invoice Amount for the previous 13 months (July 2021 - July 2022), the expression used is: 

  =Sum({<INVOICEDATE = {">=$(=Date(AddMonths(Max(INVOICEDATE),-24),'YYYY-MM-DD'))<=(=Date(AddMonths(Max(INVOICEDATE),-12),'YYYY-MM-DD'))"}>} INVOICEAMT)

Measure 1 is a bar, Measure 2 is a line so that the audience can easily see the differences between the last and previous years' invoicing amounts.  

My issue is that because I have made the Invoice Date a month, the first and last month are adding together, in this example, July 2022 and July 2023.  I need to be able to separate them. When I use the month and year, it have the line on top of the bars because the X axis expands to all 26 months.  

Below I am attaching what I have created so far in Qlik and then underneath what I have been doing manually in Excel.  I am trying to automate this dashboard.  

It stands to note that I'm just an end user and am not able to load scripts or create master calendars.  

Current Chart in QlikSense

Visualization in Excel

 TIA!

[Oleg_Troyansky]

Hi,

You certainly need to use something more detailed than just a Month, if you want to differentiate this July and last year's July... I'd use something like this for the Dimension (using the original InvoiceDate):

date(MonthStart(InvoiceDate), 'MMM')

This way, your field will be a dual field with the Month Start date as the numeric part and Month name as the text. July will appear twice, because the corresponding numbers will be different.

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

[lshorey]

That gave unexpected results and now only displays 12 months.  I did go and adjust my expression for the previous 13 months (the green line) because I found that it was bringing in too many months. It is now 

=Sum({<INVOICE_DT = {">=$(=Date(AddMonths(Max(INVOICE_DT),-24),'YYYY-MM-DD'))<=$(=Date(AddMonths(Max(INVOICE_DT),-12),'YYYY-MM-DD'))"}>} BILLING_AMT)

I validated it with a test table. 

 Any thoughts on how to fix?    

Result after using [=Date(MonthStart(INVOICE_DT),'MMM')]

[Oleg_Troyansky]

Hmm, it looks like the chart combines the months based on their names, despite the date numbers being different - that used to be different in the past, AFAIK...

You need to find a way of differentiating the names for months 1 and 13. If I had to do it, I'd probably add a field in the Master Calendar table and assign the adjusted Month name, based on dates.  In your example, the current July would be called "Jul", and the previous July would be called "Last Jul", for instance.

[marcus_sommer]

I think it's not solvable with the normal dimensionality because there is an overlapping by the months and year and also by the months against the months.

If you use the normal 12 calendar-month as dimension you could directly plot multiple years against them. But as far as there are more as 12 months the months itself couldn't differentiate them sufficiently. This means a further information needs to be added to make them unique, for example like hinted any kind of year-month. But if such dimension is used you couldn't assign multiple years to it because each dimension-value contained already a year-information. 

More difficulties are caused from the fact that you didn't want to show values from 26 months else from 25 months - because the min. months from the current period should be also the max. month from the previous period. With many many forces it might be bypassed because in general it's possible to plot each value against each dimension but such approaches have so massive disadvantages that's not sensible in any way.

The only possibility which I could currently imagine to solve the described scenario would be to implement an appropriate designed The As-Of Table - Qlik Community - 1466130 whereby I doubt that it would be trivial.

An easier possibility would occur if the requirements could be a bit adjusted, for example to show really 26 months which would mean in regard to your example that the max. month of the previous period would be the Jun 2022 and not Jul 2023. In this case the months could be clustered in 13. steps in which they would react like the above described 12 month per calendar year. Such clustering could be done with the calendar by using an expression like:

mod(YEAR * 12 + MONTH, 13) + 1 as 13MonthCluster

which should be technically be working but the dimension-values would be 1 - 13. But with a bit extra efforts like a distinct concatenating the year-month values of the last 13 months within a string-variable from the the dimension-value picked their string-representation, maybe something like this:

dual(subfield('$(var)', '|', 13MonthCluster, 13MonthCluster)

whereby there might be also more elegant approaches for it.

Beside all of the above I could imagine that skipping this dimension and using instead 25/26 expressions might much more practically. Yes, it's a bit ugly to copy & paste + adjust so many expressions is not very nice and it may not work very well with lines but with bars and points.

Another approach from the good old QlikView days would be just to use multiple charts and overlapping them - maybe it's meanwhile also possible with Sense maybe not native but by any extensions.