Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
MK9885
Master II
Master II

easy question probably about custom label for month selection

Hi,

I have a requirement where I have a table which shows me 12 months of data if I select any month

So if I select Mar-2018 table shows me

Jan, Feb+Jan, Mar+Feb+Jan, Apr+Mar+Feb+Jan and so on... for 12 months and dimension here is YearMonth.

This data is based on actuals and budget

So if I select Mar

The data for months less than or equal to Mar should have label as Actual 

and Date from Apr to Dec should have label as Budget

I tried quite a few expressions but doesn't seem to get it.

This label is dynamic... I can select any month here but selected and previous months are always Actuals in label, months falling after current selection are labeled as budget.

any help is appreciated.

@sunny_talwar

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

You want something like this?

image.png

Try this as your calculated dimension

=Aggr(Only({1} YearMonth) & Chr(13) & If(Only({1} [Year Month Num]) <= Only(TOTAL [Year Month Num]), 'Actual', 'Budget'), YearMonth)

View solution in original post

22 Replies
sunny_talwar

Is your Month field created in the script like this?

Month(DateField) as Month

if it was... then you can try this for label for Jan's label

If(JanMonthLabelExpression <= MonthSelection, 'Actual', 'Budget')

Do the same for all the month labels

MK9885
Master II
Master II
Author

Yes thats the Month field I have
My chart has YearMonth as column (dimension)
and I have single expression
So
Jan, Feb, Mar, Apr, May.... Dec
I'm using an extension where I Dimension label is on top which is months and then for expression also I can have labels for each month.
So is there a way to have single expression for label?
Ex:
If I select Apr then anything less than equal to apr is Actual and rest as Budget.
sunny_talwar

Can you share your dimension names and expressions that you are using?

MK9885
Master II
Master II
Author

3 dimensions
2 are stored into variables
1 is YearMonth
Exp is quite long please check your inbox.
thanks.
sunny_talwar

What is setFY variable? how do you define this?

MK9885
Master II
Master II
Author

Date = {">=$(=Date(YearStart(varMaxDate)))<=$(=Date(YearEnd(varMaxDate)))"}, $(sModifyFriTid)

And
sModifyFriTid variable is[YearQuarter]=,[YearMonth]=,[YearWeek]=,Year=,Quarter=,Month=,Week=
varMaxDate variable is
=IF(GetSelectedCount(Year)>0 or GetSelectedCount(Month)>0 or GetSelectedCount(YearMonth)>0, Max(%KeyDate),$(varToday))

sunny_talwar

Okay try this calculated dimension instead of using YearMonth

Aggr(YearMonth & Chr(13) & If(Only({1} YearMonth) <= YearMonth, 'Actual', 'Budget'), YearMonth)
MK9885
Master II
Master II
Author

I used it and all the months disappeared except selected month
It summing up all the un selected months in budget.

Ex: I selected Nov
I get 3 columns now instead of all 12
Total
Budget (Jan to Oct and Dec)
Then Nov as 3rd column
MK9885
Master II
Master II
Author

I used that in my expression label to test it out
I get Nov-2018 Budget for all the 12 months as label