Data Disappears after QMC Reload

[terry_kim1]

Hi Team,

I've got the weirdest issue where data disappears from Pivot Table after QMC Reload. 

When I reload on my desktop QlikView the values show up but after a scheduled reload from QMC most of the values disappear. All the data is on the server location.

The weirdest thing is 'YTD 2021' formula is very similar to 'YTD 2020' but 2021 values are showing and 2020 values aren't. I'm guessing this has to do with the formula somehow. Please see below formulas. 

Desktop reload

QMC reload

MAT 2021 (not showing)

Sum({<[Customer Stream]={'Aged Care'}, Year=, Month=, Date=, RebateDate={">=$(=Num(MonthStart(AddMonths(monthend(Max(RebateDate)),-11)))) <=$(=Num(AddMonths(monthend(Max(RebateDate)),0)))"}>} [Net Sales])

MAT 2020 (not showing)

Sum({<[Customer Stream]={'Aged Care'}, Year=, Month=, Date=, RebateDate={">=$(=Num(MonthStart(AddMonths(Monthend(Max(RebateDate)),-23)))) <=$(=Num(AddMonths(Monthend(Max(RebateDate)),-12)))"}>} [Net Sales])

YTD 2021 (showing)

Sum({<[Customer Stream]={'Aged Care'}, Month=, RebateDate={">=$(=Num(YearStart(Max(RebateDate))))<=$(=Monthend(Max(RebateDate)))"}>} [Net Sales])

YTD 2020 (not showing)

Sum({<[Customer Stream]={'Aged Care'}, Year=, Month=, RebateDate={">=$(=Num(YearStart(AddYears(Max(RebateDate),-1)))) <=$(=Num(AddYears(Monthend(Max(RebateDate)),-1)))"}>} [Net Sales])

Please help! Thank you in advance.

[rubenmarin · Accepted solution]

Hi @terry_kim1, The showing expression doesn't has a Num() funtion for the <= part, maybe that's a hint, maybe something related with date formats.

You can remove table tables so you can the expression used with $-expanded values as they are being applied.

[marcus_sommer]

How does it look if you don't reload the application with the desktop client else just opening it? Are all data (tables + fields + number of records) there and expressions return the expected values?

Differences it regard of the data might be caused from a section access which is applied by opening an application. Further changes may come from differently set user- and/or document-settings and/or the region-settings from the OS.

Ideally you start to investigate the matter with the desktop client on the server machine and with the services-user and with your normal user to exclude respectively to confirm that section access and/or any settings are differently.

- Marcus

[terry_kim1]

Hi Marcus & @rubenmarin , Thank you for your responses. 

As the application is reloaded by QMC every hour, when I open it with Desktop client it is still missing the values. It is only when I reload it with the Desktop client the values seem to return. 

In 2020, the values were showing up just fine after QMC reload and I believe there was a QlikView server upgrade early this year. Presuming it is some sort of setting / date format difference as mentioned by @rubenmarin. We'll ask our Support team to look into this further! 

-Terry

[benvatvandata]

I believe @rubenmarin has the right idea... when I tested your expressions with my own data, I was getting the same issue you are having... Can you try just removing 'Num()' functions from all of them? When I did that in my test it seemed to fix the problem.

Thanks,

Ben

[terry_kim1]

Thanks Ben, I took 'Num()' function off and the values are showing up after QMC reload. 

The only issue is the MAT and YTD calculation seems to be off but to know it is showing up now!