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

Sum up to a selected month, even if only one month is selected (aka YTD)

If a user selects Nov then one column on a table shows the MTD for Nov. This works fine, however, I also want to add another column that shows YTD for Nov. I can get the right value if the user selects Jan-Nov but i want it to work if they only have Nov selected. I think this can be done with set analysis but im not sure. Here's what i've been trying:


{$<SaleMonth={'<only(Month)'} >}

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

Might be a mismatch in how the months are presented. QlikView will basically match text to text with set analysis. So if one month is like 'Nov' and the other is like '11', that could cause a problem. So I'd try converting it to a number to be sure:

{<Month=,SaleMonth={"<$(=num(only(Month)))"}>}

View solution in original post

6 Replies
johnw
Champion III
Champion III

If they are ONLY selecting a single month, and you only want the year to date up to that one month, then yes, you can do it with set analysis. It's hard to give an exact expression without seeing your application, but I'm guessing this minor modification to what you already had (double quotes and dollar sign expansion):

{<SaleMonth={"<$(=only(Month))"}>}

Not applicable
Author

Thanks for the quick reply John.

After adding that it seems like only the month of Nov is being summed. My sales table is linked to the calendar table. I don't know if this has something to do with it?

johnw
Champion III
Champion III

It might. In some cases, you also have to tell it to ignore other fields in the calendar. I don't remember exactly which situations, though obviously if you've also selected, say, a SaleWeek field. So perhaps something like this if you also had SaleDate, SaleWeek and SaleQuarter fields in your calendar?

{<SaleMonth={"<$(=only(Month))"},SaleDate=,SaleWeek=,SaleQuarter=>}

Not applicable
Author

Yes, it appears that the linkage is the problem. If I do this i get the right result:

{$<[Month]=, SaleMonth={'<11'}>}

The only problem is i dont get the right result if i use:

{$<[Month]=, SaleMonth={"<$(=only(Month))"}>}

Getting there...

johnw
Champion III
Champion III

Might be a mismatch in how the months are presented. QlikView will basically match text to text with set analysis. So if one month is like 'Nov' and the other is like '11', that could cause a problem. So I'd try converting it to a number to be sure:

{<Month=,SaleMonth={"<$(=num(only(Month)))"}>}

Not applicable
Author

Thanks so much John! It works perfectly now.