![Contributor III](/html/@7F22A031F429FE81AFED819B1E5863E0/rank_icons/Community_Gamification-Ranking-Icons_16x16-Contributor.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to write the below excel formula in qlik using set analysis
Look into the below attachment
![Specialist III](/html/@0E4DF50C0A668F4F5116BEB444E8EE31/rank_icons/Community_Gamification-Ranking-Icons_16x16-Specialist.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Code from Excel to Qlik:
=if( LEN(TRIM([DELIVERED FORMATTED])) =0 , '' ,
if( TRIM([ORDER STATUS]) = 'DELIVERED' , '•' ,
if( [DELEVERY WINDOW STATRTIME] <= [DELIVERED FORMATTED] , TRUE ,
if( [DELIVERED FORMATTED] <= [DELEVERY WINDOW ENDTIME] , FALSE ,
'NA'
)
)
)
)
![Contributor III](/html/@7F22A031F429FE81AFED819B1E5863E0/rank_icons/Community_Gamification-Ranking-Icons_16x16-Contributor.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can u write the same expression in Qlik
By using set analysis By using Count?
![Specialist III](/html/@0E4DF50C0A668F4F5116BEB444E8EE31/rank_icons/Community_Gamification-Ranking-Icons_16x16-Specialist.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Contributor III](/html/@7F22A031F429FE81AFED819B1E5863E0/rank_icons/Community_Gamification-Ranking-Icons_16x16-Contributor.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Ajay
Below are the fields
1. POS Order NO
2.Order Status
3. Delivered (Not delivered formatted)
4.[ Delivery Window StartTime]
5.[Delivery Window EndTime]
So the logic is Count the Pos orders where the
Delivered field is less than Delivery Window Start time field
And Delivered field is more than the Delivered window End Time field and order status is equal to delivered
Can u come up with an expression using set analysis
![MVP](/html/@09D7DA47DFD811898EDA3D072B967C58/rank_icons/Community_Gamification-Ranking-Icons_16x16-MVP.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
set analysis used with aggregated function. But the formula which you share as image don't have any aggregated function. It is just comparing field and assigning value.
I will recommend you to write this expression in Script only.
or you have to use if statement which @ajaykakkar93 suggested.
Regards,
Prashant Sangle
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
![Contributor III](/html/@7F22A031F429FE81AFED819B1E5863E0/rank_icons/Community_Gamification-Ranking-Icons_16x16-Contributor.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ignore the image in that attachment
Follow the one where I described about those five fields.
![MVP](/html/@09D7DA47DFD811898EDA3D072B967C58/rank_icons/Community_Gamification-Ranking-Icons_16x16-MVP.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
then provide sample data & required output. It is difficult to understand.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
![](/skins/images/DA93A62CB05DE8EBAC29EA917B0E5CFF/responsive_peak/images/icon_anonymous_message.png)