Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

balajisj30
New Contributor III

Calculate previous month sales - Qlik Sense

Hello People

To calculate previous month sales, I wrote the exp as mentioned below but i get null values when certain month in selected in filter pane. Please help me to solve this issue.

=Sum({$<Month={"$(=Max(Month-1))"}>}sales)

7 Replies
MVP
MVP

Re: Calculate previous month sales - Qlik Sense

May be this

=Sum({$<Month={"$(=Max(Month)-1)"}>}sales)

But this isn't a good approach because what happens with you are in Month 1... Max(Month) - 1 becomes 0.... Better to perform set analysis on MonthYear or Date field..

or use The As-Of Table

balajisj30
New Contributor III

Re: Calculate previous month sales - Qlik Sense

Hi

This code also not working. I need to calculate selected month and previous month.

I have been using this set analysis exp for many apps. It was working fine but in this chart it shows null.

MVP
MVP

Re: Calculate previous month sales - Qlik Sense

What is the format of your Month field? How do you create Month in script?

balajisj30
New Contributor III

Re: Calculate previous month sales - Qlik Sense

I had period values in format YYYY0MM. So i extracted first 4 letters as year and last 2 letters as month in data manager.

i have been using this extraction for many apps. i have tried previous year sales in another app with same expression, it works well.

MVP
MVP

Re: Calculate previous month sales - Qlik Sense

First of all, it is good to know that it works elsewhere, but it doesn't really help us here, does it? I don't know what are the differences between the two applications ..

Coming back to the format, is it YYYY0MM or YYYYMM? Why an extra 0? Also, do you use Date#() in the script when you create Month field or is it a number field (or worse a text field)?

balajisj30
New Contributor III

Re: Calculate previous month sales - Qlik Sense

0 is in middle between year and month(Its taken from client db and i have confirmed with them that the format is YYYY0MM). I extract year and month as num field.

MVP
MVP

Re: Calculate previous month sales - Qlik Sense

What is the script you use to create it in the back end?