Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

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

PrashantSangle

Hi,

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

Provide more details.

Kind Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable
Author

Why does -1 not work?

trdandamudi
Master II
Master II

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


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


PrashantSangle

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable
Author

Unfortunately this this did not work

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

Not applicable
Author

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

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

sunny_talwar

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)