topic Re: Add 7 days to variable date in set analysis in QlikView

Add 7 days to variable date in set analysis

darren_dixon — Wed, 06 Dec 2017 17:11:06 GMT

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:

ID	Date	Balance
100	12/03/2017	£10.00
100	19/03/2017	£25.00
100	26/03/2017	£40.00

Wanted Result As Straight Table:

ID	Week 1	Week 2
100	£25.00	£40.00

Current expression for Week 1:

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

Thanks,

Darren

Re: Add 7 days to variable date in set analysis

Gysbert_Wassenaar — Wed, 06 Dec 2017 17:16:05 GMT

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])

Re: Add 7 days to variable date in set analysis

its_anandrjs — Wed, 06 Dec 2017 17:36:49 GMT

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])

Re: Add 7 days to variable date in set analysis

darren_dixon — Thu, 07 Dec 2017 10:48:18 GMT

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.

ID	Week 1	Week 2 (vStart Date)-7	Week 3 (vStart Date)-14	Week 4 (vStart Date)-21
100	19/03/2017 (Set by variable)

Hope this helps.

Thanks,

Darren

Re: Add 7 days to variable date in set analysis

Gysbert_Wassenaar — Fri, 08 Dec 2017 12:43:57 GMT

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')