Flag Based on Selected Period

[Anonymous]

My issues is that I have preferred suppliers that change from year to year. I want to be able to report on preferred supplier spend depending on the year selected by the end user.

Right now my approach is to create an apply map based on the table below:

Supplier	2014	2015	2016
Supplier1	Y	N	Y

My idea would be to create three different fields ... i.e Preferred 2014 Flag, Preferred 2015 Flag, Preferred 2016 .. but there are many issues that come with this approach and I am not sure it is going to work.

Does anyone have another suggestion? The end goal would be for Supplier1's spend to be calculated under preferred for 2014 and 2016, but not 2015.

Thank you,

- dave

[Gysbert_Wassenaar]

I suggest you post a small qlikview document that illustrates the problem.

[Anonymous]

Gysbert - Please see attached. These is an extremely simplified version, but it represents the issue.

In my real data model I have one fact table, and one calendar table. If I introduce the preferred data this way I fear I will have multiple synthetic keys. I also believe there is a more sophisticated approach I am missing.

Any help is appreciated.

[Gysbert_Wassenaar]

Does this do what you want: sum({<Preferred={'Y'}>}Spend)

[Anonymous]

Yes. That set analysis does what I want, but I am more concerned with the overall approach I am taking. The data sets I am working with a much larger, and I want to make sure I do not have any synthetic keys.

I am just overthinking this?

[Gysbert_Wassenaar · Accepted solution]

The synthetic key in your example is no problem and shouldn't be with large data sets. I don't think you need to remove it. But you could merge the Preferred table with the Fact table with a join or a mapping table+applymap. You can also replace the Y/N values with dual (Y,1) (N,0) values so you can multiply them with the Spend values.

[Anonymous]

Thank you Gysbert! Appreciate the help.