Total Calculation issue / Conditional Dimensions / Pivot Table

[Anonymous]

Hello,

I have this pivot table report created in a Qlikview app where it has a conditional dimension called COV_MTH_DATE (which is like the incurred date concept for accounting). When I broke down the report at that level, I could see there were 1,919,665 members signed for our service in Dec 2016,  and were also reported in our accounting system during the same reporting month (RPTG_MTH_DATE). The Member Months fields included some restatements made in 12/31/2016 reporting period to prior incurred periods.

However, when I removed this conditional dimension to check the total Members and Member Months amount, I got the correct number for member months, but Members was showing blank.

I'm sharing my script below as well. This report is for trend analysis, so I had to ignore selections on fields related to periods.

Any ideas why this is happening?

Thank you in advance!

set analysis for Members:

IF(RPTG_MTH_DATE=COV_MTH_DATE, sum( {$<ACTIVITY = {MBR}, I2F_YEAR = {'$(vMaxYear)','$(vPriorYear)'}, COV_YEAR=, RPTG_YEAR=, RPTG_MTH_DATE=, COV_MTH_DATE=,I2F_MTH_DATE =, I2F_QUARTER_YEAR= >} NET_AMOUNT ), null())

set analysis for Member Months:

sum( {$<ACTIVITY = {MBR}, I2F_YEAR = {'$(vMaxYear)','$(vPriorYear)'}, COV_YEAR=, RPTG_YEAR=, RPTG_MTH_DATE=, COV_MTH_DATE=,I2F_MTH_DATE =, I2F_QUARTER_YEAR= >} NET_AMOUNT )

[Anil_Babu_Samineni]

Does this possible to share application to test

[sunny_talwar]

May be try this for Member's expression

If(Only({$<ACTIVITY = {MBR}, I2F_YEAR = {'$(vMaxYear)','$(vPriorYear)'}, COV_YEAR=, RPTG_YEAR=, RPTG_MTH_DATE=, COV_MTH_DATE=, I2F_MTH_DATE =, I2F_QUARTER_YEAR=>} RPTG_MTH_DATE) = Only({$<ACTIVITY = {MBR}, I2F_YEAR = {'$(vMaxYear)','$(vPriorYear)'}, COV_YEAR=, RPTG_YEAR=, RPTG_MTH_DATE=, COV_MTH_DATE=, I2F_MTH_DATE =, I2F_QUARTER_YEAR=>} COV_MTH_DATE),

Sum({$<ACTIVITY = {MBR}, I2F_YEAR = {'$(vMaxYear)','$(vPriorYear)'}, COV_YEAR=, RPTG_YEAR=, RPTG_MTH_DATE=, COV_MTH_DATE=, I2F_MTH_DATE =, I2F_QUARTER_YEAR=>} NET_AMOUNT),

Null())

In order to get values for member's expression, you need to make sure that you ignore (use same set expression) the same set analysis as your main expression for the fields within the if statements also, otherwise those fields will become null as soon as you make selection, and the if statement won't be evaluated as true anymore.

[Anonymous]

Thank you very much Sunny. Your script did fix one of my problems that the report wasn't showing any prior year data.

Unfortunately this doesn't fix the total issue for Members. It's still showing "-", while Member Months is ok...

[sunny_talwar]

Not sure what all dimensions you have, but try this

Sum({$<ACTIVITY = {MBR}, I2F_YEAR = {'$(vMaxYear)','$(vPriorYear)'}, COV_YEAR=, RPTG_YEAR=, RPTG_MTH_DATE=, COV_MTH_DATE=, I2F_MTH_DATE =, I2F_QUARTER_YEAR=>} Aggr(

If(Only({$<ACTIVITY = {MBR}, I2F_YEAR = {'$(vMaxYear)','$(vPriorYear)'}, COV_YEAR=, RPTG_YEAR=, RPTG_MTH_DATE=, COV_MTH_DATE=, I2F_MTH_DATE =, I2F_QUARTER_YEAR=>} RPTG_MTH_DATE) = Only({$<ACTIVITY = {MBR}, I2F_YEAR = {'$(vMaxYear)','$(vPriorYear)'}, COV_YEAR=, RPTG_YEAR=, RPTG_MTH_DATE=, COV_MTH_DATE=, I2F_MTH_DATE =, I2F_QUARTER_YEAR=>} COV_MTH_DATE),

Sum({$<ACTIVITY = {MBR}, I2F_YEAR = {'$(vMaxYear)','$(vPriorYear)'}, COV_YEAR=, RPTG_YEAR=, RPTG_MTH_DATE=, COV_MTH_DATE=, I2F_MTH_DATE =, I2F_QUARTER_YEAR=>} NET_AMOUNT),

Null())

, AllDimensionsInYourChartSeparatedByCommaHere))