Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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)'} >}

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

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

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)))"}>}

6 Replies
MVP
MVP

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

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

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

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?

MVP
MVP

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

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

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

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...

MVP
MVP

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

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

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

Thanks so much John! It works perfectly now.

Community Browser