topic [ASK] sum total not show in QlikView

[ASK] sum total not show

rendiyan — Wed, 19 Oct 2016 12:27:20 GMT

Dear Qlik Master,

I have a req that need to split dimension into 2 chart using calculated dimension, and have to calculate actual value of 1 dimension to compare to another dimension.

simulation like below :

My calculated condition for above screenshot is :

if([Group Lv1]='REVENUES' or [Group Lv1]='COST OF REVENUES',[Group Lv1])

And my actual rev expression is :

Sum(TOTAL<YEAR>

YEAR={'$(=$(vMaxYear))'},

[PL Group Lv1]={'REVENUES'}

MOVEMENT_BALANCE)

For above screenshot there is no issue, but when i create my second chart with different definition of dimension, my actual rev doesn't show up.

Screenshot is below :

my calculated dimension condition for above (second screenshot) is :

if([Group Lv1]='OPERATING EXPENSES',[Group Lv1])

For my both chart, i use suppress when value is null.

But my actual rev doesn't show up in my second chart.

Is there any solution?

Thank You,

Best Regards,

Re: [ASK] sum total not show

shraddha_g — Wed, 19 Oct 2016 12:29:45 GMT

what expression you have used for 2nd chart

Re: [ASK] sum total not show

sunny_talwar — Wed, 19 Oct 2016 12:30:00 GMT

Would you be able to share a sample to test this out?

Re: [ASK] sum total not show

stabben23 — Wed, 19 Oct 2016 12:31:40 GMT

Change calculation to this maybe.

Sum(TOTAL<YEAR>

YEAR={'$(=$(vMaxYear))'},

[PL Group Lv1]={'OPERATING EXPENSES'}

MOVEMENT_BALANCE)

Re: [ASK] sum total not show

rendiyan — Wed, 19 Oct 2016 12:31:59 GMT

Dear Shraddha,

My expression for second chart is the same as my first one.

Sum(TOTAL<YEAR>

YEAR={'$(=$(vMaxYear))'},

[PL Group Lv1]={'REVENUES'}

MOVEMENT_BALANCE)

Re: [ASK] sum total not show

rendiyan — Wed, 19 Oct 2016 12:33:59 GMT

Hi Staffan,

The objection why i show Actual Rev is to calculate every actual of each group to actual of revenue.

So the general formula is :

[Actual of each Group] / [Actual of Group Revenue]

Thank You

Re: [ASK] sum total not show

rendiyan — Wed, 19 Oct 2016 12:43:35 GMT

Hi Sunny,

PFA.

Thank You

Re: [ASK] sum total not show

stabben23 — Wed, 19 Oct 2016 12:45:40 GMT

sum({<[Group Lv1]={'OPERATING EXPENSES'}>}MOVEMENT_BALANCE)/Sum(TOTAL<YEAR> < YEAR={'$(=$(vMaxYear))'}, [PL Group Lv1]={'REVENUES'}, >} MOVEMENT_BALANCE)

And no calculated Dimension, just [PL Group Lv1]

Re: [ASK] sum total not show

Anonymous — Wed, 19 Oct 2016 12:46:44 GMT

Try

sum({1<YEAR= {'2016'},[PL Group Lv1]={'REVENUES'}} MOVEMENT_BALANCE)

with hardcoded Year then substitute with variable.

Re: [ASK] sum total not show

sunny_talwar — Wed, 19 Oct 2016 12:48:18 GMT

Try this

Dimension

Lv 1

Expression

Sum({<[Lv 1] = {'OPERATING EXPENSES'}>}Actual)

Sum(TOTAL<YEAR>{<YEAR={'2016'}, [Lv 1]={'REVENUES'}>}Actual) * Avg({<[Lv 1] = {'OPERATING EXPENSES'}>}1)

Re: [ASK] sum total not show

rendiyan — Wed, 19 Oct 2016 12:48:45 GMT

i've awared that if i use no calculated dimension, it will work just fine.

But the requirement makes me have to split my chart.

And that's when my sum total doesn't show up.

Re: [ASK] sum total not show

rendiyan — Wed, 19 Oct 2016 12:56:04 GMT

works like charm.

But there's one curiousity.

In your expression, my formula is multiply to yours : Avg({<[Lv 1] = {'OPERATING EXPENSES'}>}1)

and what's that for?

Thank You

Re: [ASK] sum total not show

sunny_talwar — Wed, 19 Oct 2016 12:59:17 GMT

If I remove that, you will see two rows

You can use an if statement to do that same thing.

If([Lv 1] = 'OPERATING EXPENSES', Sum(TOTAL<YEAR>{<YEAR={'2016'}, [Lv 1]={'REVENUES'}>}Actual))

But I prefer using expressions without if statements and that's why multiply with that expression

Re: [ASK] sum total not show

rendiyan — Wed, 19 Oct 2016 13:09:15 GMT

nice explanation.

thank you a lot Sunny