Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
eganba07
Partner - Contributor
Partner - Contributor

Dynamic Date Variable

Hello everyone,

Trying to create a dynamic date variable for the user to track changes over time. I cannot show the data as it is embargoed and cannot share.

The goal would be for the user to set a start date and and end date. In the kpi, it would be written as such:

sum({$<date = {vEndDate}>Amount) - sum({$<date = {vStartDate}>Amount). But I see no ability to create this type of variable. Is this possible?

Labels (1)
1 Solution

Accepted Solutions
eganba07
Partner - Contributor
Partner - Contributor
Author

So I figured it out.

The process is actually not too difficult. 

Step 1: Create two blank variables. For mine I created vStartDate and vEndDate.

2) Create two qsVariable extensions.

3) Under 'Variable' enter in one of your blank variables ie: vStartDate and for 'Show as' select 'Drop down.'

4)Under the 'Values' selection select 'Dynamic' for 'Fixed or Dynamic Values'

5) In the expression for 'Dynamic Values' use: 

='$(=Concat(distinct date_received_str,'|'))' with 'date_received_str' as your date field whatever it may be. 

 

You can then use the variable in your kpis or charts. For instance, for my KPI showing initial amount I used:

=sum({<date_received_str={"$(vStartDate)"}>}amount) and it worked perfectly.

View solution in original post

2 Replies
jwjackso
Specialist III
Specialist III

Not a variable, but you could use 2 filter panes with alternate states.  Create alternate state SDATE for the starting date filter pane and alternate state EDATE for the ending date filter pane.  Then use a formula like this

sum({$<date = {"=$(=Max({EDATE}[date]))"}>Amount) - sum({$<date = {"=$(=Min({SDATE}[date]))"}>Amount)

[date] would be from your calendar.  

eganba07
Partner - Contributor
Partner - Contributor
Author

So I figured it out.

The process is actually not too difficult. 

Step 1: Create two blank variables. For mine I created vStartDate and vEndDate.

2) Create two qsVariable extensions.

3) Under 'Variable' enter in one of your blank variables ie: vStartDate and for 'Show as' select 'Drop down.'

4)Under the 'Values' selection select 'Dynamic' for 'Fixed or Dynamic Values'

5) In the expression for 'Dynamic Values' use: 

='$(=Concat(distinct date_received_str,'|'))' with 'date_received_str' as your date field whatever it may be. 

 

You can then use the variable in your kpis or charts. For instance, for my KPI showing initial amount I used:

=sum({<date_received_str={"$(vStartDate)"}>}amount) and it worked perfectly.