Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to calculate of sale, number of units sold per week.if i select last year, then it shoould be retail quanity /52(number of weeks in a year). if i select current year from period filter, suppose current month is jan then calculation be like retail quantity/4(number of weeks in jan month) if current month is aug then calculation should be retail quanity/36(number weeks till august) and if the current month is oct then calculation be like retail quantity/40(number of weeks till oct). Attaching you the qvw file for your refrence.
I'm unsing the below code,with this i'm not getting the desried output. This problem is on the sales tab and ros text object in the dashboard
='Current Year:'& Num((Sum({<week = {">=$(vMinDate)<=$(vMaxdate)"}>} RTL_QTY)/Count(DISTINCT( week))/1000000),'##.00')
& ' Last Year:'&num((((Sum({<week = {">=$(vRollingStart)<=$(vRollingEnd)"},Period=>} RTL_QTY)/Count(DISTINCT( week))
-
Sum({<week = {">=$(vRollingStart)<=$(vRollingEnd)"},Period= {'LYTD'}> }RTL_QTY)/Count(DISTINCT( week)),'##.00')
Something like this?
='Current Year:'& If(GetFieldSelections(Year)<>Max(TOTAL Year), Num((Sum({<week = {">=$(vMinDate)<=$(vMaxdate)"}>} RTL_QTY)/Count(DISTINCT( week))/1000000),'##.00'), Num((Sum({<week = {"<=$(=Max(week))"}>} RTL_QTY)/Count(DISTINCT( week))/1000000),'##.00'))
& ' Last Year:'& If(GetFieldSelections(Year)<>Max(TOTAL Year), num((((Sum({<week = {">=$(vRollingStart)<=$(vRollingEnd)"},Period=>} RTL_QTY)/Count(DISTINCT( week))-
Sum({<week = {">=$(vRollingStart)<=$(vRollingEnd)"},Period= {'LYTD'}> }RTL_QTY)/Count(DISTINCT( week)),'##.00'), Num((Sum({<week = {"<=$(=Max(week))"}>} RTL_QTY)/Count(DISTINCT( week))/1000000),'##.00'))
it's throwing a error in expression ')'expected with the below code
='Current Year:'& If(GetFieldSelections(Year)<>Max(TOTAL Year), Num((Sum({<week = {">=$(vMinDate)<=$(vMaxdate)"}>} RTL_QTY)/Count(DISTINCT( week))/1000000),'##.00'), Num((Sum({<week = {"<=$(=Max(week))"}>} RTL_QTY)/Count(DISTINCT( week))/1000000),'##.00'))
& ' Last Year:'& If(GetFieldSelections(Year)<>Max(TOTAL Year), num((((Sum({<week = {">=$(vRollingStart)<=$(vRollingEnd)"},Period=>} RTL_QTY)/Count(DISTINCT( week))-
Sum({<week = {">=$(vRollingStart)<=$(vRollingEnd)"},Period= {'LYTD'}> }RTL_QTY)/Count(DISTINCT( week)),'##.00'), Num((Sum({<week = {"<=$(=Max(week))"}>} RTL_QTY)/Count(DISTINCT( week))/1000000),'##.00'))
dataset and qvw file atttached
Have a look at the following Design Blog post, believe that may be of some further help to you:
https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344
If you want to do some further looking around, use the following URL for that:
https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog
This will also kick the post back up to where someone else may have another look at things too.
Regards,
Brett