Set Analysis for certain Point in Time with a twist

[Anonymous]

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

[Gysbert_Wassenaar]

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

[sunny_talwar]

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

[Anonymous]

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.

[Anonymous]

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

[sunny_talwar · Accepted solution]

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