Previous year sales with Month in Dimension

[BhaskarSharma]

Hi,

I need to show "Same Month Previous Year" Sales numbers (As shown below in Column E) in a table. The month itself is in the dimension. My current expressions are:

Sales: Sum({<[Report Type] = {'Monthly'}, AS_OF_DATE = >}Sales)

Sales Previous Month: Above (Sum({<[Report Type] = {'Monthly'}, AS_OF_DATE = >}Sales)) 

Need help with the expression for "Sales Prev Year".  

Appreciate any help!

Regards,

Bhaskar

[vamsee]

How does your "As of calendar" look like?
Does it have a different report type like "previous year"
If not build something like

PERIOD	Month Year	Report Type
202401	202301	PY Monthly
202402	202302	PY Monthly
202403	202303	PY Monthly
202404	202304	PY Monthly
202405	202305	PY Monthly
202406	202306	PY Monthly
202407	202307	PY Monthly
202408	202308	PY Monthly
202409	202309	PY Monthly
202410	202310	PY Monthly
202411	202311	PY Monthly
202412	202312	PY Monthly
202301	202301	Monthly
202302	202302	Monthly
202303	202303	Monthly
202304	202304	Monthly
202305	202305	Monthly
202306	202306	Monthly
202307	202307	Monthly
202308	202308	Monthly
202309	202309	Monthly
202310	202310	Monthly
202311	202311	Monthly
202312	202312	Monthly
202401	202401	Monthly
202402	202402	Monthly
202403	202403	Monthly
202404	202404	Monthly
202405	202405	Monthly
202406	202406	Monthly
202407	202407	Monthly
202408	202408	Monthly
202409	202409	Monthly
202410	202410	Monthly
202411	202411	Monthly
202412	202412	Monthly

In your expression use PY Monthly
Join the tables on Month Year, Use PERIOD as dimension 

[BhaskarSharma]

Let's simplify it a bit... now I have a simple expression Sum(Sales). I have achieved the Previous Month, and Prev Year Same Month using Above function. However, the issue with Above function is if users sort the columns (which you cannot restrict in Qlik Sense), then my calculations will go for a toss. Need an alternative to Above function to get the same result. Or may be a way to restrict the sorting (then I can use the Above function) 

 

Regards,

Bhaskar

[Kushal_Chawda]

@BhaskarSharma  try below expression. You need to sort the expression with above using aggr function to work as expected. Before to that, you need to convert your month in NUMERIC format as it looks text format (It is left aligned, correct format should be right aligned).  You can create MonthYear field using Date(Date,'MMM-YYYY') or MonthName(Date) function. Selection of MonthYear field needs to be excluded in set expression as selection of MonthYear may results in unexpected results without it

=sum(aggr(above(total sum({<MonthYear>}Sales),12),(Month,(NUMERIC,ASCENDING))))

[BhaskarSharma]

I was hoping there would be some simpler way to achieve it 🙂

[vamsee]

Not sure if you understood my previous response
Re iterating to help you better

If you have a calendar table join the below table to it, if not create one using the below 

Calendar:
Left Keep(Fact) //Left Join (Calendar) //Left Keep this table to your fact using the column Month Year
Load * Inline [
PERIOD,	Month Year,	Report Type
202401,	202301,	PY Monthly
202402,	202302,	PY Monthly
202403,	202303,	PY Monthly
202404,	202304,	PY Monthly
202405,	202305,	PY Monthly
202406,	202306,	PY Monthly
202407,	202307,	PY Monthly
202408,	202308,	PY Monthly
202409,	202309,	PY Monthly
202410,	202310,	PY Monthly
202411,	202311,	PY Monthly
202412,	202312,	PY Monthly
202301,	202301,	Monthly
202302,	202302,	Monthly
202303,	202303,	Monthly
202304,	202304,	Monthly
202305,	202305,	Monthly
202306,	202306,	Monthly
202307,	202307,	Monthly
202308,	202308,	Monthly
202309,	202309,	Monthly
202310,	202310,	Monthly
202311,	202311,	Monthly
202312,	202312,	Monthly
202401,	202401,	Monthly
202402,	202402,	Monthly
202403,	202403,	Monthly
202404,	202404,	Monthly
202405,	202405,	Monthly
202406,	202406,	Monthly
202407,	202407,	Monthly
202408,	202408,	Monthly
202409,	202409,	Monthly
202410,	202410,	Monthly
202411,	202411,	Monthly
202412,	202412,	Monthly
202401,	202312,	SEQ MTD
202402,	202401,	SEQ MTD
202403,	202402,	SEQ MTD
202404,	202403,	SEQ MTD
202405,	202404,	SEQ MTD
202406,	202405,	SEQ MTD
202407,	202406,	SEQ MTD
202408,	202407,	SEQ MTD
202409,	202408,	SEQ MTD
202410,	202409,	SEQ MTD
202411,	202410,	SEQ MTD
202412,	202411,	SEQ MTD
];

Then in the chart use PERIOD as dimension
Measure1:
Current

//Current 
Sum(Sales)

//Previous Month
Sum({<[Report Type] ={'SEQ MTD'}>}Sales)

//Previous Year
Sum({<[Report Type ={'PY Monthly'}>}Sales)

With this approach you don't have to worry about sort or any selections it will still show the correct value.

[Kushal_Chawda]

@BhaskarSharma  It’s not that complex