topic Re: Sum Total YTD (variable) in QlikView

Sum Total YTD (variable)

Tue, 23 Jun 2015 10:24:14 GMT

Hello,

I have created the following table:

Here the expression I have used for the "Cost %" for the current month

num(sum(Cost)/ Sum(total(Cost)), '#,0%')

My problem is with the expression I have created for the "Cost %" for the YTD:

num(sum({<vYTD>}Cost)/ Sum(total({$<$(vYTD)>}Cost)), '#,0%')

What am I doing wrong? How can I calculate the percentage of the cost for the YTD? Or how can I calculate the total with a Variable?

Thank you in advance for your help!

Iuri

Re: Sum Total YTD (variable)

sunny_talwar — Tue, 23 Jun 2015 10:30:17 GMT

Give this a try:

Num(Sum({<vYTD>}Cost)/Sum(TOTAL {<$(vYTD)>}Cost), '#,##0.0%')

Re: Sum Total YTD (variable)

sunny_talwar — Tue, 23 Jun 2015 10:31:18 GMT

Actually how do you define your variable vYTD?

Re: Sum Total YTD (variable)

settu_periasamy — Tue, 23 Jun 2015 10:31:58 GMT

Hi,

What Contains your vYTD variable?

Re: Sum Total YTD (variable)

Tue, 23 Jun 2015 10:33:37 GMT

Hello Sunny,

Thanks for your reply.

That does not work as well, even if the problem were the number format that would show any value, but all I get is '-'.

Re: Sum Total YTD (variable)

Tue, 23 Jun 2015 10:35:01 GMT

Hello Settu,

Here whats inside the variable:

Month=,RelativeMonthID=, MonthID={"<=$(=Max(MonthID))"}, YearID={$(=Max(YearID))}

Thanks for your help.

Re: Sum Total YTD (variable)

sunny_talwar — Tue, 23 Jun 2015 10:35:20 GMT

What is this variable vYTD? Can you show the expression behind this variable? Is it working for YTD Cost???

Re: Sum Total YTD (variable)

sunny_talwar — Tue, 23 Jun 2015 10:37:49 GMT

I did not just change the number formatting, but also removed the parenthesis after TOTAL, did you try to remove that from your expression:

num(sum({<vYTD>}Cost)/ Sum(total({$<$(vYTD)>}Cost)), '#,0%')

Re: Sum Total YTD (variable)

Tue, 23 Jun 2015 10:40:46 GMT

Here the Varible:

Month=,RelativeMonthID=, MonthID={"<=$(=Max(MonthID))"}, YearID={$(=Max(YearID))}

Re: Sum Total YTD (variable)

Tue, 23 Jun 2015 10:42:43 GMT

Yes you Right!

It came up a large number, but I will validate that is correct.

Thank you very much

Re: Sum Total YTD (variable)

sunny_talwar — Tue, 23 Jun 2015 10:50:41 GMT

That worked???

Re: Sum Total YTD (variable)

Tue, 23 Jun 2015 10:52:37 GMT

Yes it did, thank you very much!

The way I was think "total ()", it seems that does not work in the same way with variables.

I already marked as correct.

Re: Sum Total YTD (variable)

sunny_talwar — Tue, 23 Jun 2015 10:54:19 GMT

Hahahaha ya, I saw the correct answer pop-up two times before also, so I was not sure if this was truly the correct answer or not. But I am glad it worked out for you.

Best.

Sunny