topic Re: Previous year sales with Month in Dimension in App Development

Previous year sales with Month in Dimension

BhaskarSharma — Thu, 12 Sep 2024 16:28:16 GMT

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

Re: Previous year sales with Month in Dimension

vamsee — Thu, 12 Sep 2024 21:35:15 GMT

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

Re: Previous year sales with Month in Dimension

BhaskarSharma — Fri, 13 Sep 2024 10:50:31 GMT

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

Re: Previous year sales with Month in Dimension

Kushal_Chawda — Fri, 13 Sep 2024 11:06:55 GMT

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

Re: Previous year sales with Month in Dimension

BhaskarSharma — Fri, 13 Sep 2024 13:29:16 GMT

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

Re: Previous year sales with Month in Dimension

vamsee — Fri, 13 Sep 2024 18:33:58 GMT

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.

Re: Previous year sales with Month in Dimension

Kushal_Chawda — Fri, 13 Sep 2024 19:46:23 GMT

@BhaskarSharma It’s not that complex