Apples to Apples comparison

[madnanansari]

I am working with Retail Sales. I have developed a dashboard where I show Sales, Gross Profit, YTD Sales, Last Year YTD Sales for each store.

I have a requirement where the management requires an apple to apple comparison. What I mean is I would like to compare YTD sales of each store vs Last Year's YTD Sales.

The standad dashboard shows whatever sales data is available inside. For example if a store is closed mid of last year; Its listed in the dashboard. Show 0 for YTD Sales but shows some Sales figures for Last Year YTD. This is not apples vs apples comparison.

By comparing apples to apples means we need to show only those stores which are operational now. We dont need to show those stores closed last year with zero sales, but some figures for YTD Last Year. So for example for all stores the YTD Last Year sales is 1 Million but when we remove those stores closed last year, the figure for YTD Last Year will be less than 1 M. So this is now the realistic picture.

Let me know how we can do that.

[kfautsch]

I have a business case for the same apples to apples comparison. We want to show results for properties with scores in both 2016 and 2017. Any property which was only scored in only one of the two years should be excluded from the aggregation.

Has anyone found a good way to filter to show this?

Thanks for your assistance!

[Anonymous]

You can use an 'if' statement to subset to only the rows that have both the YTD values as non-zero. You will have to add this statement in all the 4 columns like I have explained below:

ifelse(YTD_sales <> 0, YTD_sales, null)

ifelse(Last_Year_YTD_sales <> 0, Last_Year_YTD_sales, null)

After doing the above, unclick on "show nulls" so that you see only the rows that have legit values. Hope this helps.

Happy Qlik-ing!

[RSvebeck]

You solve this using Set Analysis and the P "possible" parameter.

You can with this take sales for all stores this year that also has sales previous year, etc.

Syntax can be complex for a beginner, but you will find many examples in this forum. search for "Set Analysis Possible Values".

If you still get nowhere, I can make a small example later this week.

Regards

Robert