topic Aet Analysis Aggrigation Expression - Please assist create sum of previous periods in QlikView

Aet Analysis Aggrigation Expression - Please assist create sum of previous periods

orital81 — Tue, 21 Feb 2012 10:04:18 GMT

I am trying to create a calculation that will show me last 3 months Qty.

The complex issue here is: I need to sum Qty of 3 month back from the selection Year months

(If no selection is made then Today will be the date)

Attached my qvw File

Note: The period selection could be from either 2 sources : YM field from the table and the Year / Month selection fields on top

Thanks a lot for your time!

Ori

Aet Analysis Aggrigation Expression - Please assist create sum of previous periods

CELAMBARASAN — Tue, 21 Feb 2012 10:19:46 GMT

Hi,

Try with this expression

=Sum({<DateField={">=$(=MonthStart(NumMin(Max(DateField),Today()),-3))<=$(=MonthEnd(NumMin(Max(DateField),Today()),-3))"},Year=,Month=,YearMonth=>} Qty)

Hope it helps

Celambarasan

Re: Aet Analysis Aggrigation Expression - Please assist create sum of previous periods

v_iyyappan — Tue, 21 Feb 2012 10:32:19 GMT

Hi,

i attached sample Qv file . Hope its helpful for u.

Regards,

Iyyappan

Aet Analysis Aggrigation Expression - Please assist create sum of previous periods

orital81 — Tue, 21 Feb 2012 11:41:35 GMT

Thanks Iyyappan, the set date variable is working good but

the calculation is not correct, for example : If I select Jan-2012

I expect to see 0 since there was no Qty in Oct-Dec-2011.

Re: Aet Analysis Aggrigation Expression - Please assist create sum of previous periods

v_iyyappan — Tue, 21 Feb 2012 11:55:06 GMT

Hi,

I Changed the expression in latest 3 months in attached file. Hope its helpful for u .

Regards,

Iyyappan

Aet Analysis Aggrigation Expression - Please assist create sum of previous periods

orital81 — Tue, 21 Feb 2012 11:58:29 GMT

Still Jan-2012 shows Qty while

I expect to see 0 since there was no Qty in Oct-Dec-2011.

Re: Aet Analysis Aggrigation Expression - Please assist create sum of previous periods

v_iyyappan — Tue, 21 Feb 2012 12:00:25 GMT

hi,

Just use Latest 3 months expression.

=Sum({<Year=,Month=,YearMonth=,SetDate={'>=$(=(Num(MonthStart(AddMonths(SetDate,-3)))))<=$(=(Num(MonthEnd(AddMonths(SetDate,-1)))))'}>}TransQty)

Regards,

Iyyappan

Aet Analysis Aggrigation Expression - Please assist create sum of previous periods

orital81 — Tue, 21 Feb 2012 12:04:20 GMT

That didn't help... )-:

Re: Aet Analysis Aggrigation Expression - Please assist create sum of previous periods

lironbaram — Tue, 21 Feb 2012 12:15:10 GMT

check the attach file

it is working for on month year value or should it work

hope it a start

Aet Analysis Aggrigation Expression - Please assist create sum of previous periods

orital81 — Tue, 21 Feb 2012 13:03:29 GMT

Hi Liron

The solution looks good though it has an issue:

We have 0 Qty in May-2011, therefore the calculation will show 0 in last 3 months

When removing the condition If(Column(1)>0 ...) then it works.

Please clarify : What is the reason for this condition?

Aet Analysis Aggrigation Expression - Please assist create sum of previous periods

lironbaram — Tue, 21 Feb 2012 13:08:39 GMT

because you have YM as a dimension if you remove the condition and select YM=2011-05

the table will show YM - 2011-02 to 2011-05 because of the set analysis

to verify it shows only the selected YM you need a condition before the set analysis expression

Aet Analysis Aggrigation Expression - Please assist create sum of previous periods

orital81 — Tue, 21 Feb 2012 13:12:41 GMT

I understand, Thanks for your correct and accurate answer.