topic Re: Set Analysis for certain Point in Time with a twist in QlikView

Set Analysis for certain Point in Time with a twist

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi, Qlikview community

I have a problem I need to solve.

In my dataset I have Actual revenues and budget revenues and my users want to compare these number.

But I want to show only the current year and weeks that has passed when they open the tab.

So today is Year 2016 and week 10 then I want the user to only see that data in tables and charts. (see the qlikview file)

But sometimes the user want to see the budget for the next weeks e.g. budget for weeks 11,12,13.

So I want the options for the user to choose weeks 11, 12 and 13 and the user can see the budget for those weeks.

I use this formula to filter the data for max(year): Year ={$(=max(Year))} (see the qlikview file)

I used this formula for the budget to get only the budget fro the weeks that has passed: Date={'>=$(=WeekStart(Today(), -53))<=$(=Today())'} (see the qlikview file)

But when I use these formulas I can't choose a week that has not passed e.g. 11,12, 13. I know the reason for that?

What can I do to change that? Do I need to change the formulas or can I do something?

regards Darri

Re: Set Analysis for certain Point in Time with a twist

Gysbert_Wassenaar — Mon, 07 Mar 2016 14:46:51 GMT

Instead of $(=Today()) use something like $(=MakeWeekDate(max(Year),max(MyWeekNumber),6)) and let the user select the MyWeekNumber of their choice.

Re: Set Analysis for certain Point in Time with a twist

sunny_talwar — Mon, 07 Mar 2016 14:53:47 GMT

May be like this:

Expression for Actual Revenue

=If(GetSelectedCount(Week) = 0,

SUM({$<Year ={$(=max({<Week>}Year))}, Date ={'>=$(=WeekStart(Today(), -53))<=$(=Today())'}>}[Actual Revenue]),

SUM({$+1<Year ={$(=max({<Week>}Year))}, Date ={'>=$(=WeekStart(Today(), -53))<=$(=Today())'}>}[Actual Revenue]))

Expression for Actual Budget

=If(GetSelectedCount(Week) = 0,

SUM({$<Year ={$(=max({<Week>}Year))}, Date ={'>=$(=WeekStart(Today(), -53))<=$(=Today())'}>}[Actual Budget]),

SUM({$+1<Year ={$(=max({<Week>}Year))}, Date ={'>=$(=WeekStart(Today(), -53))<=$(=Today())'}>}[Actual Budget]))

Re: Set Analysis for certain Point in Time with a twist

Anonymous — Mon, 07 Mar 2016 15:17:35 GMT

Hi, Gysbert

with this formula I can choose week 11,12 and 13 which is what I want.

But when the user opens the tab the table shows every week and not only first 10 which I don't want.

Re: Set Analysis for certain Point in Time with a twist

Anonymous — Mon, 07 Mar 2016 15:19:35 GMT

Hi, Sunny

this solution is close to what I am thinking.

But when I choose for example week 11,12 and 13 I get all weeks from 1-13.

But I only want the weeks the user chooses.

But if the user does not choose anything is shows only week 1-10

Re: Set Analysis for certain Point in Time with a twist

sunny_talwar — Mon, 07 Mar 2016 15:23:02 GMT

Like this?

No selection

With selection

Expression for Actual Revenue

=If(GetSelectedCount(Week) = 0,

SUM({$<Year ={$(=max({<Week>}Year))}, Date ={'>=$(=WeekStart(Today(), -53))<=$(=Today())'}>}[Actual Revenue]),

SUM({$+<Year ={$(=max({<Week>}Year))}, Date ={'>=$(=WeekStart(Today(), -53))<=$(=Today())'}>}[Actual Revenue]))

Expression for Actual Budget

=If(GetSelectedCount(Week) = 0,

SUM({$<Year ={$(=max({<Week>}Year))}, Date ={'>=$(=WeekStart(Today(), -53))<=$(=Today())'}>}[Actual Budget]),

SUM({$+<Year ={$(=max({<Week>}Year))}, Date ={'>=$(=WeekStart(Today(), -53))<=$(=Today())'}>}[Actual Budget]))