topic Re: Dynamic Date Variable in App Development

Dynamic Date Variable

eganba07 — Tue, 30 Nov 2021 18:17:33 GMT

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?

Re: Dynamic Date Variable

jwjackso — Thu, 17 Jun 2021 19:29:00 GMT

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.

Re: Dynamic Date Variable

eganba07 — Fri, 25 Jun 2021 16:37:56 GMT

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.