Sub-total

[#1]

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

[sunny_talwar]

You may need to do something like this:

Sum(Aggr(YourExpression, YourDimensions)

[jonathandienst]

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.

[#4]

Thanks,

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

[#5]

Can anyone help me with the expression.

I am getting syntax error.

[jagan]

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))

[#7]

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.

[jagan]

HI,

Try like this

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

Regards,

Jagan.