Count in straight table with variable as condition & inline table

[patricesalem]

Hello

I'm calculating a service level per week (QLIK SENSE SERVER may 2021)

The week to analyse is generated by an inline load script (it is not joined to any table of my database structure).

I'm trying to get the number of orders shipped per year :

WEEK_REFERENCE = [Year_Week_Analysis]

I've tried the following expression to get the nb_of_orders_to_ship :

Count ({<WEEK_SM_REELLE_shipped_auto={Year_Week_Analysis}>} distinct COMMANDE)  => NO LUCK

Count ({<WEEK_SM_REELLE_shipped_auto={'202138}>} distinct COMMANDE)  => the expression works but for sure display the result for week 38 on all lines...not the expected result !

Count ({<WEEK_SM_REELLE_shipped_auto={"=$(vYearWeekAnalysis)"}>} distinct COMMANDE)  
     -> the variable vYearWeekAnalysis is equal to  [Year_Week_Analysis] : in that case it does not work

      -> if I set  vYearWeekAnalysis to a fixed value to 202138 then I get the expression to work (but with the same value for all lines in the table for sure).

Any idea how to get it to work bearing in mind that the inline table is not joined to any table in my structure

Thanks for your help
    

[AshutoshBhumkar]

Hi,

May I please know why cant you use WEEK_SM_REELLE_shipped_auto in the table dimension ?

As the WEEK_REFERENCE is not associated with the data, data will not split as per the weeks.

[patricesalem]

This dimension is included in a table containing 1million rows and indicated when an order has been shipped
The inline table is used to determine the week of reference to analyse to see what has been shipped and what remains to be shipped.
I can not / don’t want to include the inline data to the main table nor the contrary.
I ve tried not to use the inline table to determine the service level but then the result was not matching with our company policy to calculate the service level

Thanks for your interest

[patricesalem · Accepted solution]

So from what I understand, I need to review my database model....

It's on the way

thanks for your help