Comparing month to month data in a pivot table

[davarisg]

Hey guys,

First time posting a question.

I have a problem with one of my pivot tables that displays sales data (year to date) broken down by customer (dimension) with totals. The three expressions I have defined are "Current Year Sales", "Previous Year Sales", "Percentage difference":

Current Year Sales -> sum({$<Year={$(=max(Year))},Date={"<=$(=max(Date))"}>}[Sales Value] )

Previous Year Sales -> sum({$<Year={$(=max(Year)-1)}, Date={"<=$(=addmonths(max(Date),-12))"}>}[Sales Value])

This works fine in most cases.

I also have 3 list boxes on the same page with Year, Month and Day. When I click on a specific month and filter by it, Qlikview will automatically gray out all the days where there were no sales (like weekends/holidays etc).  The issue comes up with months where weekends are in the end of the month like in June 2019. By selecting month June, Qlikview will automatically gray out (and discard) 29/06 - 30/06 which will also do the same for the previous year sales where data was available.

Is there any way to avoid this and still keep the same functionality on the pivot table?

[Marcos_rv]

Hi how are you ? and it does not help to use a flag to mark the days that are non-working dates? you would have to build a master calendar, this would do it with the minimum and maximum date of your table, this new table should contain the fields date, year, month, non-work_date (the latter you do with a left join or with an applymap) and finally a field that is of the month period, this will be useful for comparing year-month vs. the previous one, the formula is: year (date) * 12 + month (date).

once having all that your formula would be:

Current Year Sales -> sum ({$ <Year = {$ (= max (Year))}, non-work_date = {0}, Date = {"<= $ (= max (Date))"}>} [ Sales Value])

Previous Year Sales -> sum ({$ <Year = {$ (= max (Year) -1)}, non-work_date = {0} Date = {"<= $ (= addmonths (max (Date), - 12 )) "}>} [Sales Value])

Assuming that non-work_date = 0 is working, 1 is non-working.
regards!!!!

[qliksus]

Qlikview greys out when you have no related data for the selection not based on weekends . Until you have filtered weekends or if there is no data for the weekend I don't see why it should grey that out .

[Marcos_rv]

in your metrics you are filtering the last year and the maximum date, Are you using a master calendar or does this date and year come from the registry? in the case of being a master calendar you could create a flag with the function WeekEnd () where 5 and 6 are saturday and sunday, and then you can use the analisys set that happens to you, using that flag. if it is complicated, give me a short example with expected results according to the selection. Regards!!!!

[davarisg]

I will try to build a master calendar and left join it with my data table. I think this might be the solution

[Brett_Bleess]

Hey George, here are a couple of links that might help as well in regard to the calendars:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

If you did get things sorted on your, you can post what you did and mark that as the Accepted Solution as well, so folks will know what the actual solution was in your case.

Regards,
Brett