Year to date calculation on pivot table by selected month/s

[berkarmagan]

Hi everyone,

I have a problem about ytd calculation on pivot table. I atteched an example which has the same problem. On the example as you see I ve 2 expression, first one is my formula which is not working properley, second one is basic sum calculation. I need to show ytd calculation by month. I mean if i pick februray on list box, i should see just februray column and it should be january+februray. Wıth my formula, I got true calculation on februrary but january's column also coming with februrary's calculation which shouldn't be come.

On the other hand user can pick more than 1 months,  if march and april picked, pivot table should show these 2 months with ytd by their calculation.

Thanks for now.

[#2]

Hi,

Refer the attached file.Hope it helps.

[berkarmagan]

Hi, thanks for your interest, i got the atteched one on yuor reply but it s same with mine 😞 are your sure that your attechment is yours ? Thanks again

[Anonymous]

Hi,

Should be something like this?

Best Regards,

[#5]

Hi,

This is the edited file.

[Anonymous]

did you check this?? Posted by other users..

Set Analysis for certain Point in Time

QlikView App: Set Analysis - Prior Period Comparison

Set Analysis for Rolling Periods

[Anonymous]

Is this what you want??

First remove Total from your expression, its giving you wrong output.

2nd, you have to remove MonthId as a dimension becoz as per your set analysis expression where your Date is less then max(Date), would return each and every month which comes into that window.

Hope this will help!!

[berkarmagan]

thanks for ur interest mauri, i checked ur qvw, if i pick one month on list box it s working but when i pick more than one, formula grouping all months that picked into a column. But i think i solved this, i ll attech at the end of this discussion. Thnks again.

[berkarmagan · Accepted solution]

Thanks guys for ur interest. I solved it yesterday, if anyone wonder about it ,check the code below.

Thanks again.

=IF(MonthId = 1, SUM({<MonthId = {1}, MonthId=>}TOTAL<MonthId, Account>Sales),
IF(MonthId = 2, SUM({<MonthId = {1,2}, MonthId=>}TOTAL<MonthId, Account>Sales),
IF(MonthId = 3, SUM({<MonthId = {1,2,3}, MonthId=>}TOTAL<MonthId, Account>Sales),
IF(MonthId = 4, SUM({<MonthId = {1,2,3,4}, MonthId=>}TOTAL<MonthId, Account>Sales),
IF(MonthId = 5, SUM({<MonthId = {1,2,3,4,5}, MonthId=>}TOTAL<MonthId, Account>Sales),
IF(MonthId = 6, SUM({<MonthId = {1,2,3,4,5,6}, MonthId=>}TOTAL<MonthId, Account>Sales),
IF(MonthId = 7, SUM({<MonthId = {1,2,3,4,5,6,7}, MonthId=>}TOTAL<MonthId, Account>Sales),
IF(MonthId = 8, SUM({<MonthId = {1,2,3,4,5,6,7,8}, MonthId=>}TOTAL<MonthId, Account>Sales),
IF(MonthId = 9, SUM({<MonthId = {1,2,3,4,5,6,7,8,9}, MonthId=>}TOTAL<MonthId, Account>Sales),
IF(MonthId = 10, SUM({<MonthId = {1,2,3,4,5,6,7,8,9,10}, MonthId=>}TOTAL<MonthId, Account>Sales),
IF(MonthId = 11, SUM({<MonthId = {1,2,3,4,5,6,7,8,9,10,11}, MonthId=>}TOTAL<MonthId, Account>Sales),
IF(MonthId = 12, SUM({<MonthId = {1,2,3,4,5,6,7,8,9,10,11,12}, MonthId=>}TOTAL<MonthId, Account>Sales),

))))))))))))

[Anonymous]

Hi,

You can refer the attached qvw..this is not the best way but you can try it.

Thanks.