Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Giansonn
Creator
Creator

Using total with count, set expression and date

I am trying to run an expression whereby I calculate the total given a set date.

The date I retrieve using min(stock_week) which works fine.

I then try to do:

=count({$<stock_week={'(min(stock_week)'}>} total scan_id)

This expression however gives me zeros see attached, why is this the case?

STR Set Expression.PNG

NOTE: I have also tried:

=count({$<date(stock_week,'DD/MM/YYYY')={"(date(min(stock_week),'DD/MM/YYYY'))"}>} total scan_id)

1 Solution

Accepted Solutions
kaushiknsolanki
Luminary Alumni
Luminary Alumni

Your expression should be

=count({$<stock_week={"$(=min(stock_week))"}>} total scan_id)

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

5 Replies
kaushiknsolanki
Luminary Alumni
Luminary Alumni

Your expression should be

=count({$<stock_week={"$(=min(stock_week))"}>} total scan_id)

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

Giansonn
Creator
Creator
Author

Thanks for your input Kaushik,

 

I have now gone into the data load to make sure that the dates are the same format but I am still having issues with dates elsewhere (I have now left this set expression and I am working on another one. This one was left unresolved).

 

These are the formats in the data load:

date(order_fixed_date - (WEEKDAY(order_fixed_date)-1),'DD/MM/YYYY') as sale_week

date(added_stamp - (WEEKDAY(added_stamp)-1), 'DD/MM/YYYY') as stock_week

 

Both variables are made so that they have a cast type of date in postgres before any SQL transformations.

These variables are the same in some observations (see attachment)

Set analysis dates table snippet.PNG

 

However when I do  =count({<stock_week=sale_week>} scan_id)/min({<stock_week=sale_week>} stock_added)

the chart tells me that there is not data because it contains only undefined values. Can you understand why that is the case? Again thanks for your support.

Giansonn
Creator
Creator
Author

The same for =count({<stock_week={'sale_week'}>} scan_id)/min({<stock_week={'sale_week'}>} stock_added)

Giansonn
Creator
Creator
Author

Okay I am fixing this using a flag:

 

in the dataload I am doing:

join(STR)
Load
scan_id,
If(stock_week = sale_week, 1, 0) as week_flag
Resident STR;

 

I don't think set analysis is made for dates so I would suggest doing everything in the data load

Giansonn
Creator
Creator
Author

Hi Kaushik, again thanks for the answer, I am one step closer now.

 

Please see attached pictures. The formula is no longer broken however I am not seeing the right numbers.

These are the numbers I should see:STR 1.PNG

And these are the numbers that I now have:

STR 2.PNG

I have also added the min stock week for the observation for greater clarity, I want to total scans that have the same stock week as min(stock_week)