topic Re: Cumulative totals issue in QlikView

Cumulative totals issue

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have below straight table:

CYTD approvals	CYTD Claims	PYTD approvals	PYTD Claims
1200	300	1100	200

The dimension is

Month(hidden)

Expressions:

Mon - only({CrdtMonthNumber = {$(=Max({<Year = { $ =Max (Year))}>}CrdtMonthNumber ))}>}Month)

CYTD approvals = sum(Total {<Year = {=Max(Year))}>}Approvals)

CYTD Claims = sum(Total {<Year = {=Max(Year))}>}Claims)

PYTD approvals = sum(Total {<Year = {=Max(Year)-1)}>}Approvals)

CYTD Claims = sum(Total {<Year = {=Max(Year)-1)}>}Claims)

Now the requirement is , we have to add classes dimension to this. We have 3 Classes and so the table should have 3 rows . Something like this for the above table.

Classes	CYTD approvals	CYTD Claims	PYTD approvals	PYTD Claims
A	600	100	500	100
B	300	100	300	50
C	300	100	300	50

When added the Classes dimension its not distributing the values. Rather its showing the total in all the rows. Any help would be appreciated.

Thanks,

Anupama Jagan

Re: Cumulative totals issue

sunny_talwar — Mon, 12 Jun 2017 18:45:51 GMT

May be change your expressions to this

CYTD approvals = sum({<Year = {=Max(Year))}>}Approvals)

CYTD Claims = sum({<Year = {=Max(Year))}>}Claims)

PYTD approvals = sum({<Year = {=Max(Year)-1)}>}Approvals)

CYTD Claims = sum({<Year = {=Max(Year)-1)}>}Claims)

Remove all the totals.... or this

CYTD approvals = sum(Total <Month> {<Year = {=Max(Year))}>}Approvals)

CYTD Claims = sum(Total <Month> {<Year = {=Max(Year))}>}Claims)

PYTD approvals = sum(Total <Month> {<Year = {=Max(Year)-1)}>}Approvals)

CYTD Claims = sum(Total <Month> {<Year = {=Max(Year)-1)}>}Claims)

Re: Cumulative totals issue

Anonymous — Tue, 13 Jun 2017 09:23:54 GMT

Hi Sunny

Both are not yielding the right result.

This table shows the cumulative total till current month for this year and previous year appr.

So in this table

CYTD approvals	CYTD Claims	PYTD approvals	PYTD Claims
1200	300	1100	200

1200 CYTD approvals shows the cumulative total till current month of this year. this 1200 i need to split to 3 classes A,B,C

Likewise for CYTD Claims ,PYTD approvals and PYTD Claims

Re: Cumulative totals issue

Anonymous — Tue, 13 Jun 2017 10:30:27 GMT

Hi Sunny,

In your expression instead of Month when i replaced Class I'm able to get the total split into 3 but its getting repeated for 6 times. Is there way to limit it to just one?

CYTD approvals = sum(Total <Class> {<Year = {=Max(Year))}>}Approvals)

CYTD Claims = sum(Total <Class> {<Year = {=Max(Year))}>}Claims)

PYTD approvals = sum(Total <Class> {<Year = {=Max(Year)-1)}>}Approvals)

CYTD Claims = sum(Total <Class> {<Year = {=Max(Year)-1)}>}Claims)

Re: Cumulative totals issue

sunny_talwar — Tue, 13 Jun 2017 10:53:42 GMT

Would you be able to share a screenshot of what you are seeing?

Re: Cumulative totals issue

Anonymous — Tue, 13 Jun 2017 10:57:51 GMT

Hi Sunny,

I will not be able to take a screenshot as im working in remote desktop.

But after replacing the month with Class this is how im getting the result

Classes	CYTD approvals	CYTD Claims	PYTD approvals	PYTD Claims
A	600	100	500	100
B	300	100	300	50
C	300	100	300	50
A	600	100	500	100
B	300	100	300	50
C	300	100	300	50
A	600	100	500	100
B	300	100	300	50
C	300	100	300	50
A	600	100	500	100
B	300	100	300	50
C	300	100	300	50
A	600	100	500	100
B	300	100	300	50
C	300	100	300	50
A	600	100	500	100
B	300	100	300	50
C	300	100	300	50

1200 is split into 3 but the same set is repeated six times

Re: Cumulative totals issue

sunny_talwar — Tue, 13 Jun 2017 11:01:54 GMT

May be add this if statement to your expressions

=If(Month = Max(TOTAL Month), Expression1)

and so on...

Re: Cumulative totals issue

Anonymous — Tue, 13 Jun 2017 11:19:00 GMT

Hi Sunny,

When i added the If condtion im getting values for Dec rather than Jun.

Re: Cumulative totals issue

sunny_talwar — Tue, 13 Jun 2017 11:20:23 GMT

lol okay... this is probably not going to work... unless you share a sample or something else to look at... I might not be able to offer help ... But I hope someone else can

Re: Cumulative totals issue

Anonymous — Tue, 13 Jun 2017 11:31:47 GMT

Hi Sunny,

I have attached a sample data and i think all the dimensions i have used in the expression are there..

CrtdMonthNumber is nothing but i have assigned numbers to the months..

Sample app isn't possible as i'm working in remote desktop

Appreciate all your help..

Re: Cumulative totals issue

sunny_talwar — Tue, 13 Jun 2017 11:40:36 GMT

One more thing... would you be able to let me know the output you will see based on this sample you provided?