topic Re: Set analysis, month Shift in QlikView

Set analysis, month Shift

Tue, 04 Sep 2012 10:26:28 GMT

Hello.

I m working on a chart which start at one specified month (implementation date), but I would like to change it in order to start my graph one month earlier.

but when I change it doesn t work, and in the frame I have, "no data to display"

The following formula work:

= Sum({$*1<Implementation_Dates ={'<>'''}, Year_Months={'>=$(=Min(Implementation_Dates))'}>}IF(Price_Evolution > 0.03, (Gross_Quantities))) *100

/ Sum({$*1<Implementation_Dates ={'<>'''}, Year_Months={'$(=Min(Implementation_Dates))'}>}TOTAL IF(Price_Evolution > 0.03, (Gross_Quantities)))

But I want to start one month earlier than "min(implementation date", then I tried the following formula, but it s not working.

= Sum({$*1<Implementation_Dates ={'<>'''}, Year_Months={'>=$(=Addmonth(Min(Implementation_Dates),-1))'}>}IF(Price_Evolution > 0.03, (Gross_Quantities))) *100

/ Sum({$*1<Implementation_Dates ={'<>'''}, Year_Months={'$(=Addmonth(Min(Implementation_Dates),-1))'}>}TOTAL IF(Price_Evolution > 0.03, (Gross_Quantities)))

I also tried with a variable in the set analysis: ....Year_Months={'$(=implemoins1)'}...with implemoins1 = addmonths(min(Implementation_Dates),-1) it s not working either

Could you tell me if someone have an idea to figure out this problem

thank you

Jonathan

Re: Set analysis, month Shift

rajni_batra — Tue, 04 Sep 2012 10:35:22 GMT

first check it in a ttext object

addmonths(min(Implementation_Dates),-1) i dont think it will return any value, from minimum value it self how it will calculate previous monthyear

Re: Set analysis, month Shift

whiteline — Tue, 04 Sep 2012 10:45:28 GMT

The problem is function Addmonth (in contrast to the min/max) returns date in a numeric format.

So you have to transform the format manualy with the date() function.

Don't know the date format you're using so cann't provide an expression.

Re: Set analysis, month Shift

Tue, 04 Sep 2012 11:16:25 GMT

@Rajni.batra I have done that it work. It Give me the month before Min(Implementation_date)

@whiteline thank you for your answer, I tried to put the date function to change the format dut it doesn't work.

Year_Months is at the format 'MMM - YYYY'

Re: Set analysis, month Shift

whiteline — Tue, 04 Sep 2012 12:55:59 GMT

I think you could try to place this expression into a variable:

=Date(Addmonth(Min(Implementation_Dates),-1), 'MMM - YYYY')

And then use it with $() in your expresison.