Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I am back with some interesting tickets. This is my new account. We had very interesting tickets answered in the past.
I want to highlight my promotion days on the Dashboard. Say In a month if I run 4 days promotion, where numbers on my line chart went up, I just want to highlight those days with description so that people will recognize why this spike on so particular day.
Just adding the event name on particular day will help users to understand why there is spike or dip. That's it!
This is very basic requirement which every dashboard should have when you are presenting to Executives. I do not know why QLIK will never considered this. Can any one help me and contribute to this?
//DOING THE MAPPING TABLE:
map_promotion:
Mapping
Load * INLINE [
Date,Promo Name
05-05-2017,Samsung Lunch
05-09-2017,Good Friday
05-12-2017,Super Bowl
];
//APPLYING THE MAPPING TABLE ON YOUR TRANSACTION TABLE:
Facts:
Load
Date,
[Transaction Count],
ApplyMap('map_promotion',Date,'Normal day') as [Promotion flag]
FROM [lib://Temp/yourQVD.QVD]
(qvd);
You now have a field called [Promotion flag] on your fact table. This field can be used in your charts to highlight dates that are not equal to "Normal day" with a new colour (using SET Analysis or "if syntax")
//Robert
Are your promotion dates same, regardless of customers and items sold?
Are you using a master calendar in your dashboard?
The promotion dates are same irrespective of any measures.
This what we use before we run date iterators:
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
Ok. Then I assume, that you do not have a master calendar. In other words you will only see dates in your dashboard where you had sales.
What you need to do in that case, is to "flag" transaction dates in your transaction table where there was a promotion, because when you do that you can make them visualized in a different color on the dashboard after the load is completed.
My recomendation would be to create a flag based on those promotion dates in your load script with a mapping table inside your transaction table.
So, your mapping table should contain only the promotion dates. When mapped on the transaction table on the transaction date they should give a new field the value 1. If not matched, the transaction date should give the value 0, name this new field "Promotion date"
Then in your visualization you are able to have differen color depending on this field, using set analysis. If you need more help, I can do an example, and if you share your script, my example could be based on your table structure.
Though, this is just one solution. There are many ways to do it, more complec and more simple, depending on your demands. Another solution for instance would be to do it completely in the interface in your expressions.
But in the end, the best solution is depending on how structured the promotion dates are. In case they are always the same days every month, or same days every week, etc, there would be even easier solutions.
Robert
Thank you so much for detailed explanation.
Can you please let me know how can I create a flag inside existing table. Do I need to create a new table with date and promotion name?
Say I have created 2 tables like this. I have same date field for both.
Or
Do you want to me flag inside table1, transaction table?
Can you please give me an example by suggesting a better way to do this?
TransactionsTable:
Date (estdate) Transaction Count
05-01-2017 300
05-02-2017 350
05-03-2017 240
05-04-2017 300
05-05-2017 850
05-06-2017 240
05-07-2017 300
05-08-2017 350
05-09-2017 740
05-10-2017 300
05-11-2017 350
05-12-2017 940
05-13-2017 350
05-14-2017 100
PromotionTable:
Date (estdate) Promo Name
05-05-2017 Samsung Lunch
05-09-2017 Good Friday
05-12-2017 Super Bowl
Thanks for you time and help.
What is the expected result?
//DOING THE MAPPING TABLE:
map_promotion:
Mapping
Load * INLINE [
Date,Promo Name
05-05-2017,Samsung Lunch
05-09-2017,Good Friday
05-12-2017,Super Bowl
];
//APPLYING THE MAPPING TABLE ON YOUR TRANSACTION TABLE:
Facts:
Load
Date,
[Transaction Count],
ApplyMap('map_promotion',Date,'Normal day') as [Promotion flag]
FROM [lib://Temp/yourQVD.QVD]
(qvd);
You now have a field called [Promotion flag] on your fact table. This field can be used in your charts to highlight dates that are not equal to "Normal day" with a new colour (using SET Analysis or "if syntax")
//Robert
It worked perfect. Thanks a lot!
Adding to this 'How can I display the event name next to date '?
If there is a change in color then we should tell why right so I want to display the name of that promotion either next to date or on Bar chart. Is there any way I can display by reading from Mapped Table?
If this also works, you should recommend this feature to Qlik.
Thanks.
Grest that it worked!
For your second question, The easy solution would be to just add a dimension column with the field [Promotion flag] next to your date field?