Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to show the last 4 weeks sale in a pivot table and cant just figure out how to.
Of course I can show the field weeknumber and make a count sold items.
But I dont want to filter at all.
Is there a way to make a SET Analysis with something like this
COUNT( {$< weeknumber = { '<= lastweek', lastweek-1, lastweek-2, lastweek-3 } >}solditems)
I know its wrong but I hope it shows what I mean
Hi Soren,
You must set the variables first to use them in your expressions:
In your menu, Settings-> Variable Overview
Then you add one variable for current week (you can use vCurrentWeek as variable name, and see the expression to get the value:
=week(today())
After that, you can use the variable in your expressions as Matthew suggested:
Count({<weeknumber = {$(vCurrentWeek)}>} solditems)
Count({<weeknumber = {$(vCurrentWeek)-1}>} solditems)
Count({<weeknumber = {$(vCurrentWeek)-2}>} solditems)
Count({<weeknumber = {$(vCurrentWeek)-3}>} solditems)
I think there is no need to use quotes but you can try it anyway.
Hope this helps, regards
Jose
Try this
COUNT( {$< weeknumber = { ">= $(=week(today())-4)<=$(=week(today()))" } >}solditems)
Hi,
If you have a datefield in your datamodel then try like this
=COUNT( {< WeekDimensionName=, MonthDimensionName=, DateDimensionName= { ">= $(=weekStart(today(),-4))<=$(=WeekEnd(today(), -1))" } >}solditems)
Regards,
jagan.
Will this not just give me solditems 4 weeks ago – I need 1 week ago, 2weeks ago, 3 weeks ago and 4 weeks ago
Fra: Mohit Sahrma
Sendt: 21. juli 2014 08:24
Til: Schou, Hans Henrik Søren (SSC009)
Emne: Re: - Help to SET Analysis - get past 4 weeks
Qlik Community<http://community.qlik.com/>
Help to SET Analysis - get past 4 weeks
reply from Mohit Sahrma<http://community.qlik.com/people/ER.Mohit?et=watches.email.thread> in App Development - View the full discussion<http://community.qlik.com/message/572243?et=watches.email.thread#572243>
You'll need to have 4 different expression columns all with different set analysis functions to get this. I would set up variables called vCureentWeek,...,vCurrentWeek-3 with the formulae
week(now())
week(now())-1
week(now())-2
week(now())-3
Then set up your 4 expressions as follows:
(Count({$<weeknumber = {'$(vCurrentWeek)'}>} solditems)
(Count({$<weeknumber = {'$(vCurrentWeek-1)'}>} solditems)
(Count({$<weeknumber = {'$(vCurrentWeek-2)'}>} solditems)
(Count({$<weeknumber = {'$(vCurrentWeek-3)'}>} solditems)
=SUM({<Month = , Year = , Quarter = , Date = {'>=$(=Date(WeekStart(Today())-28))<=$(=Date(WeekEnd(Today())-7))'}>}Sales)
This will give your Last 4 Weeks Sales... Not included current week... !
Hi Matthew
I tried your suggestion - but cant make it work.
Why cant you do something without the variables, - like this
Count({<weeknumber = {'(week(now))'}>} solditems)
Count({<weeknumber = {'(week(now)-1)'}>} solditems)
Count({<weeknumber = {'(week(now)-2)'}>} solditems)
this doesnt work, - maybe my lack of knowledge to SET Analysis
Hi Soren,
You must set the variables first to use them in your expressions:
In your menu, Settings-> Variable Overview
Then you add one variable for current week (you can use vCurrentWeek as variable name, and see the expression to get the value:
=week(today())
After that, you can use the variable in your expressions as Matthew suggested:
Count({<weeknumber = {$(vCurrentWeek)}>} solditems)
Count({<weeknumber = {$(vCurrentWeek)-1}>} solditems)
Count({<weeknumber = {$(vCurrentWeek)-2}>} solditems)
Count({<weeknumber = {$(vCurrentWeek)-3}>} solditems)
I think there is no need to use quotes but you can try it anyway.
Hope this helps, regards
Jose
You're right - the quotes are not needed. I don't know why I got into that habit, but both work.
Did Jose's advise help you find the variable settings Søren Schou? I've never been able to do this without variables so I would always use that method.
Hi Matt,
I think you need quotes when you need to evaluate a string instead of a number, I am assuming that weeknumber field is in a number format.
Best regards