Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I would like to compare in set analysis a "DateField" with a variable MonthYear.
e.g. I can calculate the last 12 month value for every month (in Aug15 I display the sum of value from Aug15 to Sep14) with also an if condition. But I would like to insert another condition linked with a "DateField" great/less than a MonthYear and I can't do that.
Here the "wrong" example of my expression, in this case I'm trying to explain what I'm looking for in Aug15:
Sum(aggr(rangesum(above(total sum({<[Rata Aperta/Chiusa]={'APERTA'} , datamonthyear([Data Pagamento Rata])={">Aug15"},
datamonthyear([Data Documento Fattura])={"<Sep15"}>} ([Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)),0,12)),
DataMonthYear))
I would like that the expression works for every MonthYear.
Hope it's clear enough.
I attach here my qvw.
Thx so much
Seems that doesn't work yet...this is from loading the new script:
Is the expression of the chart "Case Flag" correct?
Sum(aggr(rangesum(above(total sum({<[Rata Aperta/Chiusa]={'APERTA'}>}
([Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)),0,12)),DataMonthYear))
+
Sum(aggr(rangesum(above(total sum({<[Rata Aperta/Chiusa]={'CHIUSA'}, Flag={1} >}
([Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)),0,12)),DataMonthYear))
Here the qvw with the load of the new script.
Hi gwassenaar, no idea about my issue?
Some. You can try a pick-match combination to construct an expression that will calculate a different set analysis sub-expression for each month. See this document: Evaluating "sets" in the context of a dimension
Hi gwassenaar, thank for your help! I tried 3 days to understand the paper that you suggested to me, but with my Qlikview knowledge I can't understand how I can put that in my data model.
Could you please help me and explain how I can use the solutions in the paper in my data model. I completely going crazy about that.
Thank you so much.
Hi guys, any idea about my issue?
Anyone have ever seen something like that?
Excuses for the late reply. I was kinda busy this week.
Try this expression:
Pick(Match(DataMonthYear, $(=concat({<DataMonthYear={'*'}>}distinct chr(39) & DataMonthYear & chr(39),',',Period))),
$(=concat({<DataMonthYear={'*'}>}distinct
'Sum(aggr(rangesum(above(total sum({<[Rata Aperta/Chiusa]={"APERTA"}>}([Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)),0,12)),DataMonthYear))
+Sum(aggr(rangesum(above(total sum({<[Rata Aperta/Chiusa]={"CHIUSA"}, [Data Pagamento Rata] = {">' &
Num(AddMonths(Period,1)-1) &
'"}, [Data Documento Fattura] = {"<' &
Num(AddMonths(Period,1)) &
'"} >}([Importo Rata Corretto]*([Importo Carico]/[Importo EM TOT NO IVA_Corretto])/1000000)),0,12)),DataMonthYear))'
, ','
, Period)
)
)
If you don't see or understand at first glance what it does then don't worry. This is a quite advanced trick. First read the help file to understand what the pick and match functions do. Then put the concat expressions in separate text boxes to see what these do. The first creates a comma separated list of DataMonthYear values that can be used in the Match function. The second create a comma separated list of expressions for the Pick function so a DateMonthYear is matched with the correct expression.