Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
dhasharadh
Creator III
Creator III

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

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

8 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

Use not only() use max():

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

- Marcus

jagan
MVP
MVP

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
Creator III
Creator III
Author

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

Try this,

Replace single quotes with double quotes

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

marcus_sommer
MVP & Luminary
MVP & Luminary

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
Master II
Master II

Hi dhasharadh

Did you resolve your issue?

dhasharadh
Creator III
Creator III
Author

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
Master II
Master II

Hi dhasharadh

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