Cross table YTD values else zero

Wed, 20 Feb 2013 15:06:06 GMT

Hello everyone

I have a crosstable to make, I have two dimensions, one that contains monthsYear . the months Year dimension must be on top and the other dimension on the left , what I need is if there is a selected month, we need to show values of the expressions from the startof the year, till the selected month, else we show the remaining months of the year too but with zero instead of the value, for exemple if I select april if must show the values till april, however starting from may it must show zero.

thanks in advance for your help

Re: Cross table YTD values else zero

Anonymous — Thu, 21 Feb 2013 10:10:21 GMT

To summarise:

If a month is selected there is just one column shown, with the year to date amount.

If no month is selected then the months to date show the monthly amounts, and future months show 0.

Two issues:

once a month is selected, you have to then 'break out of that selection' to show the year to date amount. For this set analysis is ideal.

what if more than one month is selected (i.e. a selection of month is made but not just one). Do you then show the year to date, choosing the maximum month. I'll assume you do.

Start with an if statement to test how many distinct months are selected.

if(count(distinct Month)=count(ALL distinct Month),....,.....)

If one or more months are selected, use a set analysis statement like this:

sum({<Month=,Date={'>=YearStart(max(fielddate)) <=MonthEnd(max(fielddate))'}>} fieldvalue)

If no month is selected, use the same set analysis, but without ignoring the month, to show the months values up to the max date.

sum({<Date={'>=YearStart(max(fielddate)) <=MonthEnd(max(fielddate))'}>} fieldvalue)

If you can't get these statements working (I've not tested myself) try creating variables for the start and end dates, or post an example application.

Jonathan

topic Re: Cross table YTD values else zero in QlikView

Cross table YTD values else zero

Re: Cross table YTD values else zero