Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

darren_dixon
New Contributor III

Add 7 days to variable date in set analysis

Hi,

How do I add 7 days to the variable within the set analysis.
I want Week 1 to show the balance at 19/03/2017 then Week 2 to show the balance at 26/03/2017

Script:

SET vStartDate = '19/03/2017';

Data:

IDDateBalance
10012/03/2017£10.00
10019/03/2017£25.00
10026/03/2017£40.00

Wanted Result As Straight Table:

IDWeek 1Week 2
100£25.00£40.00

Current expression for Week 1:

only({<[Date]={'$(vStartDate)'}>}[Balance])

Thanks,

Darren

4 Replies
MVP & Luminary
MVP & Luminary

Re: Add 7 days to variable date in set analysis

The easy way out:


SET vStartDate = '19/03/2017';

SET vStartDateNextWeek = '26/03/2017';


Week 1 balance: only({<[Date]={'$(vStartDate)'}>}[Balance])

Week 2 balance: only({<[Date]={'$(vStartDateNextWeek )'}>}[Balance])



talk is cheap, supply exceeds demand

Re: Add 7 days to variable date in set analysis

From variables, you can try these ways and make it dynamically

Let  vStartDate =Date(WeekStart( Today() )-1);

Let vStartDateCurrWeek =WeekEnd( Today() );

In any text objects try this

='Previopus Week :- '&only({<[Date]={'$(vStartDate)'}>}[Balance])

='Current Week :- '&only({<[Date]={'$(vStartDateCurrWeek)'}>}[Balance])

Highlighted
darren_dixon
New Contributor III

Re: Add 7 days to variable date in set analysis

Hi,

The SET vStartDate = '19/03/2017'; will be an input box for the user to change when required.


I'm looking for a way within the expression to take away 7 days, 14 days 21 days etc. rather than just this week and last week.

IDWeek 1Week 2 (vStart Date)-7Week 3 (vStart Date)-14Week 4 (vStart Date)-21
10019/03/2017 (Set by variable)

Hope this helps.


Thanks,

Darren

MVP & Luminary
MVP & Luminary

Re: Add 7 days to variable date in set analysis

Ok, then create another variable that turns the user input into a date a week later:

vNextWeek: =Date(Date#(vStartDate,'DD/MM/YYYY'),'DD/MM/YYYY')


talk is cheap, supply exceeds demand