Questions - Set analysis on KPI

[#1]

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

[arulsettu]

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]

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

[#4]

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?

[#5]

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 · Accepted solution]

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.

[#7]

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

[#8]

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]

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