Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
patricesalem
Creator II
Creator II

Count in straight table with variable as condition & inline table

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 :

patricesalem_0-1633022263315.png

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 !

patricesalem_0-1633022894754.png

 

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

patricesalem_1-1633022700719.png

patricesalem_1-1633022941719.png

 

 


      -> 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
    


 

Labels (2)
1 Solution

Accepted Solutions
patricesalem
Creator II
Creator II
Author

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

It's on the way

thanks for your help

View solution in original post

3 Replies
AshutoshBhumkar
Partner - Specialist
Partner - Specialist

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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

It's on the way

thanks for your help