Set Analysis - Less than today + Greater than today

[Dayna]

Hello All,

I'm trying to produce a chart where I want to show a different set of results depending on the timescale, i.e. if the week is less than the week number for today, I want to show sales, but for this week or greater than this week, I want to show forecast.

The if statement I've done works fine, but when I try to add a sub total to the timescale, it doesn't calculate correctly as it only shows me the sales or the forecast.

This is the current expression I've done:

if(Timescale< year(today())&'.'&num(week(today()),'00'),

sum(tr_as_qty_loc) *-1,

if(Timescale >= year(today())&'.'&num(week(today()),'00'),

sum( [FCS_Qty]),

0))

I think I need to do it using a set analysis expression, but struggling to get the syntax! Any help would be greatly appreciated

Many thanks!

Dayna

[sunny_talwar]

My guess is that you are using a pivot table, since you mentioned the Sub-Totals. I believe that if you are getting correct values on row level, you might be able to get the sub-totals right by using the Sum(Aggr(...)) function. I suggest sharing some sample data with your expected result to help you better here.

Best,

Sunny

[alexandros17]

Unfortunately with set analysis you can include only one condition so you can easily compute

sum(tr_as_qty_loc) *-1 where Timescale< year(today())&'.'&num(week(today()),'00')

but you cannot perform a comparison between 2 or more conditions without using an if

[simenkg]

You can write it as two formulas.

(Sum({<Date={"<Today()"}>} tr_as_qty_loc) * -1)

+

Sum({<Date={">=Today()"}>} FCS_Qty)

[Dayna]

Hi Sunny,

That sounds like my problem! I've attached the application with an example product with my current application. Thank you for your help!

Kind Regards,

Dayna

[Dayna]

Hi Simen,

Something I always seem to struggle with in set analysis is date formatting - as I doesn't work on dates, but weekname. Would something like 'Sum({<Timescale={">=weekname(Today())"}>} FCS_Qty)' still have the same effect or do I have to do something fancy in QlikView?

Many thanks!

Dayna

[simenkg]

This is why you usually build a Master Calendar for your applications.

Depending on how Timescale is formatted it is possible to create a date using MakeWeekDate(Year, Week).

If Timescale has the format 2015-12, then you can add a line in your script when you load your Timescale:

MakeWeekDate(Subfield(Timescale,'-',1),Subfield(Timescale,'-',2)) as Date

I usually also create a variable when loading the application:

let vTodayDate = Floor(Today(1));

Then the set analysis becomes

Sum({<Date={">=$(vTodayDate)"}>} FCS_Qty)

+

Sum({<Date={"<$(vTodayDate)"}>} tr_as_qty_loc)