topic Re: Set Analysis stopped working after loading new data in QlikView

Set Analysis stopped working after loading new data

Tue, 03 Jan 2017 20:18:53 GMT

Hi,

I am new to posting to the forum however have been reading and developing in Qlikview for a little over 2 years now however have run into an issue that i have not seen before and am unable to find the answer for. I have a set of straight tables that utilize a set analysis expression to limit what is displayed on the table using a field called [SummaryReportFlag]. The data populates based on whether the flag is checked yes or not. The idea is to display the current 12 months of evaluation for an employee. The formula If(SUM({$<[SummaryReportFlag]={'Yes'}>}Auto_TotalProd_MonthProd)=0,'-',Sum({$<[SummaryReportFlag]={'Yes'}>}Auto_TotalProd_MonthProd)) has worked without out issue for the better part of a year during development and deployment of this tool. However this morning I loaded a new months worth of data and the expression stopped working. Now it no longer filters out the months that [SummaryReportFlag] = No. It shows all months regardless. I have uploaded a picture for reference. All of the months before Apr-16 have [SummaryReportFlag] = No, and all of the months through Mar-17 are [SummaryReportFlag] = Yes. Has anyone ever seen this before or know what might all the sudden be wrong? Please let me know if I can be more detailed or clear. I appreciate any help.

Thanks

Matt

Re: Set Analysis stopped working after loading new data

sunny_talwar — Tue, 03 Jan 2017 20:54:04 GMT

What if you use this?

If(SUM({$<[SummaryReportFlag]={'Yes'}>}Auto_TotalProd_MonthProd) > 0, Sum({$<[SummaryReportFlag]={'Yes'}>}Auto_TotalProd_MonthProd))

Re: Set Analysis stopped working after loading new data

Tue, 03 Jan 2017 20:58:05 GMT

Thanks Sunny. I tested it however it seemed to have no effect. I appreciate the input.

Re: Set Analysis stopped working after loading new data

johnw — Tue, 03 Jan 2017 21:13:13 GMT

I don't expect this to work either, but just in case:

If(SUM({$<[SummaryReportFlag]-={'No'}>}Auto_TotalProd_MonthProd)=0,'-',Sum({$<[SummaryReportFlag]-={'No'}>}Auto_TotalProd_MonthProd))

Re: Set Analysis stopped working after loading new data

Tue, 03 Jan 2017 21:32:31 GMT

I appreciate your input John. I have tested your formula and as expected it also did not work. I will offer that when I try to set the original formula to If(SUM({$<[SummaryReportFlag]={'No'}>}Auto_TotalProd_MonthProd)=0,'-',Sum({$<[SummaryReportFlag]={'No'}>}Auto_TotalProd_MonthProd)) it does not display the no, it just displays the "- " indicator. However the No's do have data in them.

Re: Set Analysis stopped working after loading new data

sunny_talwar — Tue, 03 Jan 2017 22:31:06 GMT

Can you check and see if on the Presentation tab of the chart properties do you have Check 'Suppress Zero Values' or not? Ideally, it should be checked, but I am not sure what you have it as.

Also, did you happen to change QlikView version recently? Or could there be a change in the data in the background somehow? Just throwing out few ideas to troubleshoot your issue

Re: Set Analysis stopped working after loading new data

Wed, 04 Jan 2017 12:56:10 GMT

I don't have the suppress zero-values checked. However it must stay unchecked because the future months are 0 values from the data source and need to be shown for formatting purposes. The formula I was using that had been working suppressed all of the past values while keeping the 0 values for future months intact. I don't believe there has been an update to Qlik however the way we use Qlik at the company i don't know when there has been updates as we are not privy to that information. Also we are not allowed access to any administrative area where it could be found. I have noticed something else as well. Instead of hiding the data for the 'Nos' It looks to be changing them to 0 values which was not occurring before. The formula Sum(Auto_TotalProd_MonthProd) populates all of the fields, however when I add the [SummaryReportFlag]='Yes' on the original formula

If(SUM({<[SummaryReportFlag]={'Yes'}>}Auto_TotalProd_MonthProd)=0,'-',Sum({<[SummaryReportFlag]={'Yes'}>}Auto_TotalProd_MonthProd))

Then the past months change to the '-' value which should only be changing if the value is 0 in the above formula. I tested this formula

If(SUM(Auto_TotalProd_MonthProd)=0,'-',Sum({<[SummaryReportFlag]={'Yes'}>}Auto_TotalProd_MonthProd))

which turns everything to 0 that is [SummaryReportFlag]='Yes'. I wonder if the the root problem is that somehow the expression is changing the answer to 0 in turn forcing the months to be shown? I hope all of that made sense.

Re: Set Analysis stopped working after loading new data

Wed, 04 Jan 2017 14:47:56 GMT

I honestly don't know what changed, but when I do this formula

If(SUM(Auto_TotalProd_MonthProd)=0,'-',Sum({<[SummaryReportFlag]={'Yes'}>}Auto_TotalProd_MonthProd))

It changes the current values to 0's and for some reason changes the 0s in the future to null values. Then your suggestion to Suppress Zero-Values was checked and it caused the old months to disappear and the future months to stay. However this only works where the value of the field is greater than 0. So further in the chart for one metric an employee had 0 for a previous month, once i enable this expression to show we are back at square showing all of the months. I have attached a before and after for reference. I have added the above expressions adjustment to all expressions in the chart

Before

This is how it should display however this is before i added it to the last expression.

After

Once added to the last expression where there are 0 values for multiple months it replaces them with the null indicator that i specified '-'. I believe I see what is happening however I have no idea why this would be occurring now.

Re: Set Analysis stopped working after loading new data

sunny_talwar — Wed, 04 Jan 2017 19:42:00 GMT

I am a little confused now, what is the difference between the Before and After charts? Can you share the expressions used for them? A sample will be very helpful in looking into the issue with greater depth.

Re: Set Analysis stopped working after loading new data

Wed, 04 Jan 2017 20:04:33 GMT

Sorry for the confusion above. The data i am working with is extremely sensitive so i am trying to show it without revealing to much. Every row is a identical expression except for data set it is summing. I spent the better part of the day working on this trying different solutions and have finally come up with one that works. I have posted the below expression that stopped working with the corrected solution below.

Problem-Filtering a dimension utilizing an expression for a straight table.

Expression- If(SUM({<[SummaryReportFlag]={'Yes'}>}Auto_TotalProd_MonthProd)=0,'-',Sum({<[SummaryReportFlag]={'Yes'}>}Auto_TotalProd_MonthProd))

Corrected Expression

=IF(SUM({<[SummaryReportFlag]={Yes,0}>}(Auto_TotalProd_MonthProd)=0),'-',If(Sum({<[SummaryReportFlag]={Yes}>}Auto_TotalProd_MonthProd)=0,'-',SUM({<[SummaryReportFlag]={Yes}>}Auto_TotalProd_MonthProd)))