Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Mengelhardt
Contributor III
Contributor III

Getting the selected period from filters

Hi all,

I have a special question again.

 

I have a lot of sales data from various periods (say months). For simplification they are numbered  1,2, 3,..... 

1. Task: I have to make a KPI with the sales of actual month as the first KPI and the difference to the last month as the second KPI. My unsatisfying solution so far ist to hardcode the last month in a variable. But with every new data I have to adjust that. It would be great if I could just take the max(month) for calculation but that does not work for the difference as max(month)-1 is not recognized.

 

2. Task: Take an abitrary month as a Filter and calculate the sales of this selected month (first KPI) and the difference to the preceding month (second KPI).  

Any help is highly appreciated!

 

Best Regards

Marc

 

Labels (2)
1 Solution

Accepted Solutions
HugoRomeira_PT
Creator
Creator

Hello,

Ok, I understand...

So, assuming that the current month is equal to the max(month) available, and that the month field is an incremental integer ID 🙂

Meaning:

June 2022 = 14

May 2022 = 13

April 2022 = 12

March 2022 = 11

...

 

Actual Month:

sum({<[month]={$(=Max([month]))}>}[Sales])

 

Previous Month:

 

sum({<[month]={$(=Max([month])-1)}>}[Sales])

 

 

Hope it helps!

If the issue is solved please mark the answer with Accept as Solution.
If you want to go quickly, go alone. If you want to go far, go together.

View solution in original post

4 Replies
HugoRomeira_PT
Creator
Creator

Hi Marc,

 

If the granularity of your data model allows you to see Sales per Day, it would mean that you have a Date field in the data model.

 

If yes, you could use this in set analysis and so achieve your requirement using the following expressions:

Actual Month

sum({<Date={">=$(=MonthStart(Today())) <=$(=MonthEnd(Today()))"}>}[Sales])

 

Previous Month

sum({<Date={">=$(=MonthStart(AddMonths(Today(),-1))) <=$(=MonthEnd(AddMonths(Today(),-1)))"}>}[Sales])

 

 

Hope it helps.

If the issue is solved please mark the answer with Accept as Solution.
If you want to go quickly, go alone. If you want to go far, go together.
Mengelhardt
Contributor III
Contributor III
Author

Thanks a lot Hugo,

In fact I just have the months, numerated by 1,2,..12,13,14, ...,,n. So I really need to take numbers instead of date functions.

Best Regards

Marc

 

 

HugoRomeira_PT
Creator
Creator

Hello,

Ok, I understand...

So, assuming that the current month is equal to the max(month) available, and that the month field is an incremental integer ID 🙂

Meaning:

June 2022 = 14

May 2022 = 13

April 2022 = 12

March 2022 = 11

...

 

Actual Month:

sum({<[month]={$(=Max([month]))}>}[Sales])

 

Previous Month:

 

sum({<[month]={$(=Max([month])-1)}>}[Sales])

 

 

Hope it helps!

If the issue is solved please mark the answer with Accept as Solution.
If you want to go quickly, go alone. If you want to go far, go together.
Mengelhardt
Contributor III
Contributor III
Author

Thanks Hugo! 

Great, That's it!

Have a nice day!!

Best and happy Regards

Marc