Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Sub-total

I am havinf the following formula.

If(Sum({<Year={'$(=Max(Year))'}>} [Amount (LCY)]) > 0 AND Sum({<Year={'$(=Max(Year)-1)'}>} [Amount (LCY)]) = 0 AND Sum({<Year={'$(=Max(Year)-2)'}>} [Amount (LCY)]) = 0, (Sum([Amount (LCY)])))

I am not getting sub-total and count of the above in Qlik sense KPI..

Can anyone modify and provide revised formula such that it displays count and sum total

7 Replies

Re: Sub-total

You may need to do something like this:

Sum(Aggr(YourExpression, YourDimensions)

MVP
MVP

Re: Sub-total

If you are in a straight table, change the sum method on Properties | Expressions to sum of rows and/or count of rows.

To get sum fo rows in a pivot table, you will need something like:

Sum(Aggr(

  If(Sum({<Year={'$(=Max(Year))'}>} [Amount (LCY)]) > 0 AND Sum({<Year={'$(=Max(Year)-1)'}>} [Amount (LCY)]) = 0 AND Sum({<Year={'$(=Max(Year)-2)'}>} [Amount (LCY)]) = 0, (Sum([Amount (LCY)]))),

  <table dim1>, <table dim2>, ...))

Replace <table dim1>, ... with a comma separated list of all the pivot table dimensions.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Sub-total

Thanks,

but can you please re-write the code. I am getting syntax error.

Not applicable

Re: Sub-total

Can anyone help me with the expression.

I am getting syntax error.

MVP & Luminary
MVP & Luminary

Re: Sub-total

Hi,

Try this expression

=Sum(Aggr(If(Sum({<Year={'$(=Max(Year))'}>} [Amount (LCY)]) > 0

  AND Sum({<Year={'$(=Max(Year)-1)', '$(=Max(Year)-2)'}>} [Amount (LCY)]) = 0 , Sum([Amount (LCY)])), DimensionName))

Not applicable

Re: Sub-total

Jagan,

Thanksa lot.

last question in this thread

when I select a year,  I also want the previous year's data and next years data.

I added -1 and +1 after the years.

But I am not getting it.

Can you help me with the code.

MVP & Luminary
MVP & Luminary

Re: Sub-total

HI,

Try like this

Sum({<Year={'>=$(=Max(Year) - 1)<=$(=Max(Year) + 1)'}>} [Amount (LCY)])



Regards,

Jagan.