15 Replies Latest reply: Dec 11, 2015 1:04 PM by Gysbert Wassenaar

# 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

• ###### Re: Less/greater than a variable data

Something like this:

• ###### Re: Less/greater than a variable data

Thanks Sinan,

but is not exactly what I'm looking for.

Your solution is about a static case, August 15, where [Data Pagamento Rata] must be greater than 2015-09-01 and [Data Documento Fattura] must be less than 2015-09-01.

But in the case of July 15, [Data Pagamento Rata] must be greater than 2015-08-01 and [Data Documento Fattura] must be less than 2015-08-01.

So what we need in the set analysis is to create a variable date and insert it. No fixed date like 2015-08-01, but something that works with a condition.

Is a bit clearer now?

stalwar1 have u ever implemented something like that?

• ###### Re: Less/greater than a variable data

I added two variables via an input box:

• ###### Re: Less/greater than a variable data

This is not what i'm looking for.

Maybe i'm not good about explaining my problem.

In the red square there are the right value that i would like to see in the same table. The issue is to create a variable date that changes for every DataMonthYear.

Here my qvw.

gwassenaar have u ever seen something like that?

• ###### Re: Less/greater than a variable data

The set in a set analysis expression is calculated at the chart level, not at the row level. That's why what you're trying isn't working.

Perhaps you can calculate it in the script in a preceding load since all the fields you need seem to be part of the Facts table:

Facts:

LOAD *, If( [Data Documento Fattura] > MonthEnd(Data)-1 and [Data Pagamento Rata] <= MonthEnd(Data) ,1,0) As Flag;

"REP_ID" as [ID Negozio],

text("ART_ID") as [ID Articolo],

....etc...

• ###### Re: Less/greater than a variable data

Thanks gwassenaar for your help, but I haven't got it yet.

How should I insert "Flag" in the formula of chart?

• ###### Re: Less/greater than a variable data

Use this set selector {<Flag={1}>}

• ###### Re: Less/greater than a variable data

I tried, but it doesn't work yet. Maybe I'm wrong with my formula:

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))

That's what I would like to display:

Here the new qvw with the script:

LOAD *, If( [Data Documento Fattura] > MonthEnd(Data)-1 and [Data Pagamento Rata] <= MonthEnd(Data) ,1,0) As Flag;

• ###### Re: Less/greater than a variable data
LOAD *, If( [Data Documento Fattura] > MonthEnd(Data)-1 and [Data Pagamento Rata] <= MonthEnd(Data) ,1,0) As Flag;

Hmm, I think that's a mistake by me. It should be the other way around:

LOAD *, If( [Data Pagamento Rata] > MonthEnd(Data)-1 and [Data Documento Fattura] <= MonthEnd(Data) ,1,0) As Flag;

• ###### Re: Less/greater than a variable data

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.

• ###### Re: Less/greater than a variable data

Hi gwassenaar, no idea about my issue?

• ###### Re: Less/greater than a variable data

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

• ###### Re: Less/greater than a variable data

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.

• ###### Re: Less/greater than a variable data

Hi guys, any idea about my issue?

Anyone have ever seen something like that?

gwassenaar

maxgro

stalwar1

• ###### Re: Less/greater than a variable data

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] = {">' &

'"}, [Data Documento Fattura] = {"<' &

'"} >}([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.