Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
You can write it as two formulas.
(Sum({<Date={"<Today()"}>} tr_as_qty_loc) * -1)
+
Sum({<Date={">=Today()"}>} FCS_Qty)
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
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
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)