topic Re: Less/greater than a variable data in QlikView

Less/greater than a variable data

caccio88 — Tue, 01 Dec 2015 14:26:56 GMT

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

sinanozdemir — Tue, 01 Dec 2015 18:07:23 GMT

Something like this:

Re: Less/greater than a variable data

caccio88 — Tue, 01 Dec 2015 18:50:40 GMT

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?

sunindia‌ have u ever implemented something like that?

Re: Less/greater than a variable data

sinanozdemir — Tue, 01 Dec 2015 19:30:56 GMT

I added two variables via an input box:

Re: Less/greater than a variable data

caccio88 — Wed, 02 Dec 2015 10:53:03 GMT

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

Gysbert_Wassenaar — Wed, 02 Dec 2015 17:42:32 GMT

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;

LOAD

"REP_ID" as [ID Negozio],

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

....etc...

Re: Less/greater than a variable data

caccio88 — Wed, 02 Dec 2015 18:06:33 GMT

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

Gysbert_Wassenaar — Wed, 02 Dec 2015 18:12:39 GMT

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

Re: Less/greater than a variable data

caccio88 — Wed, 02 Dec 2015 18:49:24 GMT

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

Gysbert_Wassenaar — Wed, 02 Dec 2015 19:27:23 GMT

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

caccio88 — Wed, 02 Dec 2015 21:49:00 GMT

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.

Re: Less/greater than a variable data

caccio88 — Fri, 04 Dec 2015 09:09:12 GMT

Hi gwassenaar‌, no idea about my issue?

Re: Less/greater than a variable data

Gysbert_Wassenaar — Fri, 04 Dec 2015 17:13:37 GMT

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

caccio88 — Wed, 09 Dec 2015 08:52:31 GMT

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

caccio88 — Thu, 10 Dec 2015 17:04:47 GMT

Hi guys, any idea about my issue?

Anyone have ever seen something like that?

gwassenaar‌

maxgro‌

sunindia‌

Re: Less/greater than a variable data

Gysbert_Wassenaar — Fri, 11 Dec 2015 18:04:27 GMT

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.