Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
caccio88
Creator II
Creator II

Less/greater than a variable data

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

15 Replies
caccio88
Creator II
Creator II
Author

Seems that doesn't work yet...this is from loading the new script:

Untitled5.png

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.

caccio88
Creator II
Creator II
Author

Hi gwassenaar‌, no idea about my issue?

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
caccio88
Creator II
Creator II
Author

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.

caccio88
Creator II
Creator II
Author

Hi guys, any idea about my issue?

Anyone have ever seen something like that?

gwassenaar

maxgro

sunindia

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand