PB with KPI and filters in QV 12

[edemerdjieva]

Hi everybody,

I have a strange issue with a report in a QlikView 12 environment. The report was initially built in QlikView 11. The KPI and filters worked properly. But since QV12 the functioning is different. The case is for an apparel company which sells gift cards and they are used afterwards for payment.

Purpose: retrieve all gift cards still active within the date range.

KPI formula = , Sum({EmissionOSCC<[Gift Card Remaining Value]-={0},[Date]={">=$(v_DateIssue_1) <=$(v_DateIssue_2)"},[Date Expiry]={">=$(v_DateIssue_2)"}>}[Gift Card Issued Amount ])

where "EmissionOSCC" is an alternate state used within the filters (selections of date , store)

Filters: Date issue between 01/09/2018 and 30/09/2018

The result in QV11: QV11.png (attached)

The result in QV12: QV12.png (attached)

Does anybody have a clue what is the issue in QV12 ?

Thank you in advance for your help !

[kaushiknsolanki]

Hi,

My suggestion is that you check the format of the Date parameters.

All you have to do is, remove the label of the expression and once chart is populated, keep your cursor near the label. It will show you the evaluated expression and show you the date format it has used.

Regards,

Kaushik Solanki

[Colin-Albert]

A quick test for the expression is to add the expression to your chart again, but leave the column label blank.
You can then hover your mpuse over the label in the chart which will show the expanded formula so you can see how the  expression is being interpreted by Qlik. This may indicate any differences between your v11 and v12 charts.

The problem may be a date formatting issue between the date fields and your expression. The test above will show that.

[edemerdjieva]

I did the test. It seems QV12 interprets the dates differently. Or is it the calendar object that behaves in a new way in QV12.

Here are the results:

In QV11 the date range is a "text" format (ex: 43424 for 20/11/2018). In QV12 it is a date format. But the date is stored as a text format in the .qvd file.

[edemerdjieva]

I think the issue comes from the QVD file.

In QV11 the date is generated as text (see file attached) and in QV12 it is a date.

Is there a way to manage this ? Is it a problem due to SR7 ?

[Colin-Albert · Accepted solution]

If you change your day and week key fields to be integers in your load script, it should work consistently.

floor(DAY_KEY) as DAY_KEY     

floor(WEEK_KEY) as WEEK_KEY     

This will stop the QVD loads form being optimised so will take a bit longer to load, but you can always save the QVDs again to fix this, once you have proved this is the problem.

[edemerdjieva · Accepted solution]

I modified the load script as you said. And I had to do it everywhere (in several script files). I noticed that if there is at least one field not formatted with floor QV ignores the rest of the formatting.

One more thing, I had to modify the calendar object in order to force numeric data with min(num([Date])) and max(num([Date])).

Thanks a lot for your help!