Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You may need to do something like this:
Sum(Aggr(YourExpression, YourDimensions)
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.
Thanks,
but can you please re-write the code. I am getting syntax error.
Can anyone help me with the expression.
I am getting syntax error.
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))
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.
HI,
Try like this
Sum({<Year={'>=$(=Max(Year) - 1)<=$(=Max(Year) + 1)'}>} [Amount (LCY)])
Regards,
Jagan.