topic Set analysis for calculating the ytd amount in QlikView

Set analysis for calculating the ytd amount

alvinford — Thu, 17 Feb 2011 14:32:37 GMT

Hi I am using the following set analysis for calculating the ytd amount.

sum({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}>} SalesAmount)

The problem here is it's working fine for all the dimensions except the month. I have created a drill down using the month and other dimensions. When I drilldown to the month level it's displaying the monthly values but not the cumulative value for each month.

To be very simple if I take chart with Month as Dimension and the above code as expression . Its not calculating the cummulative value for months. How can I overcome this problem ? . Is there any way to modify the expression ?

Request for your valuable inputs..

Thanks in Advance....

Regards,

Alvin.

Set analysis for calculating the ytd amount

Thu, 17 Feb 2011 14:41:34 GMT

Try that:

sum

({$<Year = {$(=max(Year))}, Month = {"<=$(=max(Month))"}>} LineSalesAmount

)

Set analysis for calculating the ytd amount

alvinford — Thu, 17 Feb 2011 16:06:02 GMT

emaC,

Thanks for your reply..

This does not meet my requiremnet .. its displaying same as the one I have used .

Regards,

Alvin.

Set analysis for calculating the ytd amount

Thu, 17 Feb 2011 16:16:55 GMT

Alvin,

You could create two variables "startyear" and "endperiod" in a format like this 'YYYYMMDD' and you field "date" you have to write in the same format. Then you can use the following expression. Your new variables could be calculated with a function or just put them like inputfields in the report.

Regards

sum( {$<date={">=$(startyear)"}*{"<=$(endperiod)"}SalesAmount)

Set analysis for calculating the ytd amount

alvinford — Thu, 17 Feb 2011 16:27:48 GMT

Hi Rengas,

Thanks for your reply ..My user doesn't want to select the period. He just wants to click the month .. The data should be displayed from April to the selected month.. . I can achieve but when I take the Month as dimension I could not achieve the cumulative value of the months . The Set analysis is giving the individual value of the month..

Regards,

Alvin.