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

Showing max year as bar and prior year as line

Hi stalwar1‌ Sunny,

I have a small issue, hope you would be able to help me out.

I have created a chart. Now I just want to show the max year in the form of bar and max(year) -1 in the form of line and get rid of any unnecessary data associated with previous years.

My max year is 2016

And prior year is 2015.

So on x-axis I'll have months from Jan -Dec. Basically bars from Jan - Sep(since I don't have data for 2016 Oct, 2016 Nov and 2016 Dec). And a line for Jan - Dec

Thanks for all the help.

1 Solution

Accepted Solutions
sunny_talwar

This should do exactly that. is it not?

Sum({<YR_MTH_KEY = {"=Month(YR_MTH_KEY) = Month(Max(TOTAL YR_MTH_KEY))"}>} CRRNT_PRCPL_BAL_AMT)

View solution in original post

10 Replies
sunny_talwar

I think the best option would be to create a Year field in the script if you are able to modify your script. Can you?

sunny_talwar

Without changes may be like this

Dimension

=Month(YR_MTH_KEY)

Expressions

Sum({<DLQ_STATUS = {1}, YR_MTH_KEY = {"=Year(YR_MTH_KEY) = Max(TOTAL Year(YR_MTH_KEY))"}>}CRRNT_PRCPL_BAL_AMT)/sum({<DLQ_STATUS = {1}>}CRRNT_PRCPL_BAL_AMT2)

Sum({<DLQ_STATUS = {1}, YR_MTH_KEY = {"=Year(YR_MTH_KEY) = (Max(TOTAL Year(YR_MTH_KEY))-1)"}>}CRRNT_PRCPL_BAL_AMT)/sum({<DLQ_STATUS = {1}>}CRRNT_PRCPL_BAL_AMT2)

Capture.PNG

Not applicable
Author

Yes I can do that Sunny. I want to create two variables in front end.

vCurrentYear = year(max(YR_MTH_KEY))

vPriorYear = year(max(YR_MTH_KEY)) - 1


And want to have two expressions as sum({<DLQ_STATUS = {1},Year ={$(vCurrentYear)}>}CRRNT_PRCPL_BAL_AMT)/sum({<DLQ_STATUS = {1},Year ={$(vCurrentYear)}>}CRRNT_PRCPL_BAL_AMT2)


sum({<DLQ_STATUS = {1},Year ={$(vPriorYear)}>}CRRNT_PRCPL_BAL_AMT)/sum({<DLQ_STATUS = {1},Year ={$(vPriorYear)}>}CRRNT_PRCPL_BAL_AMT2) . And the dimension as Month.


But the challenge is, I'm not able to convert the date field into month and year..

sunny_talwar

Why not? What is the issue you are running into while converting date field to Month and Year?

Not applicable
Author

Sunny sorry to bug you again but one last thing..

I want to show current year max month (2016 - Aug) and prior year max month of current year (2015 - Aug).

So I gave my expression as for Current year max month

Sum({<YR_MTH_KEY = {"=Month(YR_MTH_KEY) = Max(TOTAL Month(YR_MTH_KEY))"}>}CRRNT_PRCPL_BAL_AMT)

But not sure how to give the expression for prior year max month of current year.

Cycle.png

Thanks for all the help. Just can't say it enough..

sunny_talwar

May be try this (reorder Max and Month in the set modifier:

Sum({<YR_MTH_KEY = {"=Month(YR_MTH_KEY) = Month(Max(TOTAL YR_MTH_KEY))"}>} CRRNT_PRCPL_BAL_AMT)

Not applicable
Author

Sunny, this gives me max month but not previous year current month right.

Oct.png

sunny_talwar

Previous year current month? Not sure I follow?

Not applicable
Author

Say the max Month in current year is Oct, i.e 2016-Oct

Max Month in previous year would be Dec, i.e 2015-Dec

But I want 2015 Oct from prev year.

Because when comparing YOY in the charts for max month, I shouldn't compare 2016 Oct with 2015 Dec right?

I want to compare 2016 Oct with 2015 Oct.

Hope it makes sense.