Need help on Rolling to Previous 3 Months...?

[dhasharadh]

Hi Folks,

we have data from  august 2014 to Apr 2016 ,

my requirement is when i select a month it has to show the Previous 3 Months count along with the selected month ccount...

For that am using a TEMP field , am Extracting  this TEMP field from my source File , am giving numbers to Months like 1,2,3,.......

now am using this Expression :  count ( { < TEMP  = {  ' >=$(=(Only(TEMP)-3) <= $(=Only(TEMP)) ' }  > } Duns)

now its Working fine , But After few days am going to get the & years of data I need to take Month Numbers for all those years I feel it as a time taking Process( Not Best approach )...

is this the Correct Approach of doing it.. or any other methods are there....???

Note : I tried by taking Month in the Place TEMP but its not working over Years.

Tried With AutoNumber(Month + Year) as Month_Year but No use...

Please let em know if any alternate Solutions are there for this Issue..

Attaching the sample Source File , please find the attachement...

any help on this is highly appreciable....

thanks in Advance...

Best Regards

dhasharadh

[marcus_sommer]

Use not only() use max():

count ( { < Month = { ">=$(=(max(Month)-3)<=$(=max(Month))"}  > } Entity_Duns)

- Marcus

[jagan]

Hi,

Try this expression

= Sum({<Month_num ={'>=$(=Max(Month_num)-3)<=$(=Max(Month_num))'}>}Amount)

Use Max() instead of Only(), Only() fails to retrieve value when you have multiple possible values.

Hope this helps you.

Regards,

Jagan.

[dhasharadh]

Hi Marcus ,

thanks for your responce , Used Max(month) also , same thing happening..

getting the corret values upto Apr - 2015..,

if i select March - 2015 then  it has to show Previous Months in 2015 ( i .e Feb and jan ) and  Dec-2014  also...

but it is not giving as Expected..

Similar issue am getting while am selecting feb , jan 2015....

if i select feb it is showing count for only feb - 2015, and Jan -2015,last case if i select Jan -2015 then it is showing count for only jan- 2015..

Best regards

dhasharadh

[qlikmsg4u]

Try this,

Replace single quotes with double quotes

= Sum({<Month_num ={">=$(=Max(Month_num)-3)<=$(=Max(Month_num))"}>}Amount)

[marcus_sommer]

Yes of course in that simplified way wouldn't it work on the turn over years. In such cases is it very helpful to use a MonthCounter generated within the master-calendar, see: Re: Howto make a variable to calculate quarters regarding year changes?

As alternatively you could query a date within the set analysis, maybe with addmonths(), see various examples: https://community.qlik.com/search.jspa?q=set+analysis%2C+addmonths‌. But my first suggestion is easier and more performant.

- Marcus

[qlikviewwizard]

Hi dhasharadh

Did you resolve your issue?

[dhasharadh]

Hi ,

Thanks for Your Responces but none of the above like Max() or Only() or not working as Expected...

please let me know the Standard Proceure to this ...

please find the Attached Data Source....if you get any idea let me know..

Best Regards

dhasharadh

[qlikviewwizard]

Hi dhasharadh

Can you attach the sample QVW as well to demonstrate your issue? Thank you.