Dynamic Date Variable

[eganba07]

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?

[jwjackso]

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 · Accepted solution]

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.