topic Re: Count in straight table with variable as condition in App Development

Count in straight table with variable as condition & inline table

patricesalem — Fri, 15 Nov 2024 23:40:16 GMT

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

Re: Count in straight table with variable as condition & inline table

AshutoshBhumkar — Thu, 30 Sep 2021 17:41:10 GMT

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.

Re: Count in straight table with variable as condition

patricesalem — Thu, 30 Sep 2021 19:08:49 GMT

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

Re: Count in straight table with variable as condition & inline table

patricesalem — Fri, 01 Oct 2021 06:50:21 GMT

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

It's on the way

thanks for your help