Date Calculation

[#1]

Good Day Qlik-Comm,

I have a graph that shows a three month trend and I am using a variables to give me those months. The code that i use in the variable is as follows:

vCurr - month(today())&year(today())
vPrev - month(AddMonths(max(Capture_Date),-1)) &year(today())

vPrev2 - month(AddMonths(max(Capture_Date),-2)) &year(today())

and my expression is as follows:

vCurr - (DISTINCT{<MonthYear= {'$(vCurr)'},BusinessUnit={'Processing and Clearing In','Processing and Clearing Out'},[Metric_ID]={'326','329','330'}>}Data)/Count(DISTINCT{<MonthYear= {'$(vCurr)'},BusinessUnit={'Processing and Clearing In','Processing and Clearing Out'},[Metric_ID]={'326','329','330'}>}[Team Member])

vPrev - (DISTINCT{<MonthYear= {'$(vPrev)'},BusinessUnit={'Processing and Clearing In','Processing and Clearing Out'},[Metric_ID]={'326','329','330'}>}Data)/Count(DISTINCT{<MonthYear= {'$(vPrev)'},BusinessUnit={'Processing and Clearing In','Processing and Clearing Out'},[Metric_ID]={'326','329','330'}>}[Team Member])

vPrev2 - (DISTINCT{<MonthYear= {'$(vPrev2)'},BusinessUnit={'Processing and Clearing In','Processing and Clearing Out'},[Metric_ID]={'326','329','330'}>}Data)/Count(DISTINCT{<MonthYear= {'$(vPrev2)'},BusinessUnit={'Processing and Clearing In','Processing and Clearing Out'},[Metric_ID]={'326','329','330'}>}[Team Member])

The problem that i am encountering is that it is bringing up the following months:

Mar2014 , Oct2014, Sep2014

where it was suppose to be :

Mar2014, Feb2014, Jan2014

However if i drill down on any bar on the graph it displays correct and when i clear the selection it goes back to :

Mar2014 , Oct2014, Sep2014

Can anyone assist with a solution or point me in the right direction if you have had this issue before.

Thanking you in advance,

Sanjeev

[maxgro]

it seems to me there are 2 strange things:

- you use a mix of date for current and previous (today for current, capture date for previous);

add    = month(AddMonths(max(Capture_Date),-1)) in a textbox to see and debug what happens when you select or clear

- in first three months (jan...mar) you probably have a wrong year in vPrev and/or vPrev2

[#3]

Thanx Massimo.

I will try that.

[#4 · Accepted solution]

Hi

Try these

vCur = Date(AddMonths(Today(),0),'MMMYYYY')

vPrev = Date(AddMonths(Today(),-1),'MMMYYYY')

vPrev2 = Date(AddMonths(Today(),-2),'MMMYYYY')

[#5]

Thanx Ravi it works like a bomb!!!

Sanjeev