Current Month to last three month logic

[abhaysingh]

Hi all,

what is the logic if i want to show current month sales with the trail of last 3 month sale..

thanks

[jpenuliar]

You can Flag dates (if within 3 month) in your Load script

or flag MonthIndex as implemented in :

QlikView How To (or Tips & Tricks) Application by Lee Matthews

http://community.qlik.com/docs/DOC-5486

[petter]

Do you want to do it adhoc in just a specific chart or do it often in different situations in the same application.

• The first calls for a simple set expression
• The second case is better implemented in the load script.

[abhaysingh]

Could u pls explain me both

[petter]

You can in any expression within an aggregation-type function use an extended syntax called set expression:

First case:

Sum( { <Month={'Jan','Feb','March'}> } Sales )

The bold part makes a further selection or de-selection of which values in the Sales field that will be summed.

To do what you want dynamically you will have to use another feature of QlikView to calculate the current month and include 2 months more relative to that:

Sum( { <Date={">=Num(AddMonth(Today(),-2))"}>} Sales )    // Including all Sales in a time-window 3 months back

Yes - it is quite confusing with all the special symbols...

Second case:

Create an extra field in your load script and use a simple set expression.

A field has to be calculated to indicate how many months before the current month each date is - say that you name it RelativeMonth:  0 is current month, -1 is previous month ... and so forth -36 is 3 years back ...

Then you still can use Set Expression but with a much simplified expression:

Sum( { RelativeMonth={">=-2"} Sales )

I didn't test any of the expressions in QlikView - so it migth need tweaking to work properly...

Let me know... ?

[abhaysingh]

Dear petter, what would be the syntax in case of Week..

Like if i want to see Current Week Sales with last three Week Sales. Instead of AddMonth Function what i have to use for Week?.

Thanks

Abhay

[petter]

Use the same logic as the second case I descibed - calculate in your load script a field named for instance

_Week to have a relative week #:

[Dates]:
LOAD
    Date,
    Date-Today() AS _Day,
    Floor( (Date-WeekStart(Today())) / 7 ) AS _Week,

.....

.....

Then use set expression to select the relative week range to include ...

[abhaysingh]

Hey Petter,

_Day ouput and _week output Is not understandable for me. could u pls help me in it

[petter]

Well - what I give you are merely hints on a strategy to apply so you can develop your application. Plugging in my suggestions will not necessarily work - you will have to think carefully what my suggestions seem to solve and apply them with the right modifications to make them work for your application.

[petter]

When you use relative dates then you should sort in a descending order usually ... What I see in your screenshot indicates to me that you don't have dates for all normal dates ... since this is probably date-fields from your fact or transaction table... so you get holes in between ....