Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Discover how organizations are unlocking new revenue streams: Watch here
cancel
Showing results for 
Search instead for 
Did you mean: 
Poh
Contributor II
Contributor II

Period on Period comparison

I am trying to create a period to period comparison in a Button chart.

Below is the sample code:
=
//arrow
if(
(
(count({<MonthYear={'$(vMaxMonthYear)'}>}distinct id))
-
(count({<MonthYear={'$(vPreviousMaxMonth)'}>}distinct id))
)>=0
,
'▲','▼')

&

//Delta
num((count({<MonthYear={'$(vMaxMonthYear)'}>}distinct id))
-
(count({<MonthYear={'$(vPreviousMaxMonth)'}>}distinct id)),'#,##0')
&
' vs last month ('
&
//last month
num((count({<MonthYear={'$(vPreviousMaxMonth)'}>}distinct id)),'#,##0')
&
')'

However, instead of restricting it to a month on month, I want to change it to a period by period. In other words, when a user chooses 30 days in the filter, I want to dynamically compute the value for the previous period (30 days).

I have create 4 variable:
1. vStart - variable for start date of selection
2. vEnd - variable for end date of selection
3. vPStart - variable for previous period start date (vStart - count(date))
4. vPEnd - variable for previous period end date (vEnd - count(date))

This: =Count(DISTINCT {$<[Date] = {">=$(vStart) <=$(vEnd)"}>} id) is working but when I do =Count(DISTINCT {$<[Date] ={">=$(vPStart) <=$(vPEnd)"}>} id) it returns "0".

I am also guess its a filtering issue where when a user filters for 1 specific month, the data of the previous month is filter off, thus the 0 count. However, how is it that :count({<MonthYear={'$(vPreviousMaxMonth)'}>}distinct id) still works?
Labels (3)
3 Replies
hardikpatel172
Employee
Employee

What is count(date) here? 
3. vPStart - variable for previous period start date (vStart - count(date))
4. vPEnd - variable for previous period end date (vEnd - count(date))

you can try just putting $(vPStart) and $(vPEnd) in a separate textbox just to see what values are being evaluated for those variables. 

Poh
Contributor II
Contributor II
Author

Hi,

I was thinking count(date) will count the number of days between the start and end (when a filter is applied). 

With regards to your suggestion, I have split it up to 2 separate text bos but it doesnt seem to be working.

hardikpatel172
Employee
Employee

What I was suggesting is steps to troubleshoot and I will suggest that first get those two variable working PStart and PEnd and I still didn't understand how exactly you want those? 
First, try to decide what values are expecting for PStart and PEnd and how will you calculate those manually based on your selection? That would make things easier for you to define the logic for those variables. 

Feel free to share a script or screenshot of how you want and I'll be happy to help!