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: 
Not applicable

Questions - Set analysis on KPI

Hi,

I'm quite new to Qlik and would really appreciate some help here.

I'm trying to create one KPI which would not be affected by other filters. I know it should be done by set analysis but it doesn't really work out, here is what I've done so far:

=Sum({<STORE_TYPE={'Main Store'},SALES_DATE={$(vToday)},Store_No={*}>}DAILY_TOTAL_NET_SALES)

/Sum({<SALES_DATE={$(vToday)}>}OVERALL_SALES_TARGET)-1

Some background, this KPI calculated the actual sales vs target and I need to limit it to all stores in main stores and sales date to be 'vtoday'. I have multiple filters on different store so I don't want people clicking different store would affect the total picture of this KPI.

Hope it's clear and really appreciate your help.

Regards,

Jacob

1 Solution

Accepted Solutions
sam_grounds
Contributor III
Contributor III

Is the [Store_No] field the one you are making selections on? If you have a store name field that is being used, you will have to set it to ignore selections on any fields that you are using by adding [StoreName]= into the set analysis of both sums.

View solution in original post

8 Replies
arulsettu
Master III
Master III

may be try this

=Sum({<STORE_TYPE={'Main Store'},SALES_DATE={$(vToday)},Store_No=>}DAILY_TOTAL_NET_SALES)

/Sum({<SALES_DATE={$(vToday)}>}OVERALL_SALES_TARGET)-1

sam_grounds
Contributor III
Contributor III

I expect it's because your date variable holds a date as a string eg. 30/08/2017, which would need to be put in set analysis as SALES_DATE={'30/08/2017'} (needs single quotes).

How does this work for you?

=(Sum({<STORE_TYPE={'Main Store'},SALES_DATE={'$(vToday)'},Store_No=>}DAILY_TOTAL_NET_SALES)

/Sum({<SALES_DATE={'$(vToday)'},Store_No=>}OVERALL_SALES_TARGET))-1

Thanks,

Sam

Not applicable
Author

The date is correct, I did try to remove the store_no part and it does not change if I change day, the problem now is when I change different store (or different regions with multiple stores), the KPI changes as well..

any other suggestion?

Not applicable
Author

Thanks Arul, I did try your expression but it doesn't work... the part is not working is the store_no, as I did try different store type or sales date and it doesn't change, but it's still changing when I choose different stores or region... any other suggestions?

sam_grounds
Contributor III
Contributor III

Is the [Store_No] field the one you are making selections on? If you have a store name field that is being used, you will have to set it to ignore selections on any fields that you are using by adding [StoreName]= into the set analysis of both sums.

Not applicable
Author

Tried this and it's still not working (select store and KPI still change)

=Sum({<STORE_TYPE={'Main Store'},SALES_DATE={$(vToday)},[US_SITE_NO]=>}DAILY_TOTAL_NET_SALES)

/Sum({<SALES_DATE={$(vToday)},[US_SITE_NO]=>}OVERALL_SALES_TARGET)-1

or I need to set all the filters which related to store selection (i.e. district, region etc?)

Not applicable
Author

I got it now, so i need to set it with all the related store field and now it's working, thanks so much for your help!

Regards,

Jacob

sam_grounds
Contributor III
Contributor III

No problem. Glad you managed to figure it out. Yes you will need to specifically reference any field you want to ignore. If you want to ignore ALL fields, then you can simply put a 1 in there instead of the dollar. LIke so,

sum({1}Sales)

This will show ALL sales, none of your selections will count towards this expression. It will sum every record in the Sales field (subject to what you can see after taking into account set analysis). Or if you want to specify a small number of fields to include their selections you can do this like so...

sum({1<MonthField=$::MonthField,YearField=$::YearField>}Sales)

It's like saying "ignore all selections except MonthField and YearField".

Sam