Problem with monthly value comparison with Month as Dimension

[poklegoguy]

Hi all, I want to create a pivot table that has Month as one of the dimensions to show the monthly value comparison (increase or decrease %). Its fairly straightforward to use Before() function with the expression to get previous months' value but here's a problem. Let say I have data for 2024 and 2025 in the dataset and the user made a selection of year 2025, 2025-02 and 2025-03 works fine but 2025-01 will show nothing which is false as there's data available for 2024-12. Does anyone know any workaround for this? Or suggest a better way to do this kind of comparison table without using Before() function? Any help is greatly appreciated!

[sbaro_bd]

Hi @poklegoguy ,

I think you have to use a Set analysis in your previous month sales measure. Your expression should be look like this one : 

SUM( {<[YearMonth]={"$(=AddMonths(YearMonthCal,-1))"}>} Sales)

To use the AddMonths() function, your field has to be a date field to work.

Regards.

[marcus_sommer]

You may try to use a specialized dimensionality like described here:

The As-Of Table - Qlik Community - 1466130

But you may also remain by the UI interrecord-functions by wrapping it with an aggr() in which a set analysis ignored certain selections and this result is then filtered with a boolean expression.

This means within the aggr() exists a table which includes all periods and therefore all previous cell-results are available and against it comes something like: sign(count(distinct Month)) to reflect the selection state again.

[poklegoguy]

Hi @sbaro_bd, thanks for the reply. This won't work as I have Month/YearMonth as one of my dimensions as I want to list out all the months and their comparison dynamically. It will work only if I remove Month/YearMonth from the dimensions and add in all the months as measures individually and manually which isn't ideal. 

[poklegoguy]

Hi @marcus_sommer, thanks for the reply. I read the thread but can't really figure out how to integrate that to fit my case. Can you guide me how does AsOf Table and does it really work for my case?

[marcus_sommer]

Unfortunately, I couldn't explain it better as HIC it did within the blog and an implementation within a certain scenario requires a good knowledge about the existing data-model/data-set and the intended views.

My suggestion is that you at first don't try to implement it within your origin application else within a dummy with one or two small inline-tables and just a few essential fields and records - to play fast and easy with it and to comprehend the underlying logic. And then adding some more data and complexity and finally the transfer within the origin application. It may look like a detour but often it will save more efforts and time as developing any new stuff within a bigger complexity.