Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

Month processing issue


Good morning,

 

I am using the following expression as part of Set analysis to derive PT values for a month (it works)

Sum ({$<MonthExp={$(=Only(MonthExp))}>}PT)

When I try to deribe the prior month's values, I am using the following:

Sum ({$<MonthExp={$(=Only(MonthExp)-1)}>}PT) and instead of getting the previous month , i.e 'Mar' I am getting the value of 3 and not able to derive the prior month's values.

Please help.

Thank you

11 Replies
MVP
MVP

Re: Month processing issue

Value of 3? Can you may be share a screenshot or a sampl?

Re: Month processing issue

Hi,

create new variable to calculate previous month and use that in your set analysis.

Provide more details.

Kind Regards

Regards,
Prashant Sangle
Not applicable

Re: Month processing issue

Why does -1 not work?

trdandamudi
Honored Contributor

Re: Month processing issue

May be this: ( I don't know your date format, So change accordingly...)


Sum ({$<MonthExp={$(=Only(Month(MonthExp)-1))}>}PT)


Re: Month processing issue

Hi,

Qlikview store data in dual datatype.

Whenever you perform arithmetic operation on date it convert it into number .

therefore you are getting 3 instead of mar.

Kind Regards,

Regards,
Prashant Sangle
Not applicable

Re: Month processing issue

Unfortunately this this did not work

With MonthExp = 'Apr' , the result was '0' instead of 'Mar'

Not applicable

Re: Month processing issue

I changed it to be:

Sum ({$<MonthExp={$(=Only(Month((MonthExp)-1)))}>}PT)

and instead of 'Mar' it returned 'Jan'

It looks like it's going back to the beginning of the quarter?

trdandamudi
Honored Contributor

Re: Month processing issue

Can you please post a small sample app ....

MVP
MVP

Re: Month processing issue

Can you create a new field in the script and call it MonthNum

Num(Month(TempDate)) as MonthNum

and use this in your equation


Sum({$<MonthNum={$(=Only(MonthNum)-1)}>}PT)

Or completely get rid of Months because they are confusing and use dates instead:

Sum({<Date = {"$(='>=' & Date(MonthStart(Max(Date), -1), 'DateFieldFormatHere') & '<=' & Date(MonthEnd(Max(Date), -1), 'DateFieldFormatHere'))"}>} PT)