Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.
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.