Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have this formula giving me the variance between two years, making use of Flags as created in my master calendar:
sum({<CYTD={1}>} Sales)
/
(sum({<FPYTD={1}>} Sales)) - 1
YTD = Current Year-to-Date:
FPYTD: First prior Year-to-Date
And I have a QuarterYear field declared in this way:
dual('Q' & ceil(month(D)/3) & '-' & Year(D),ceil(month(D)/3)+Year(D)*10)
This gives me Q1-2017,Q2-2018,...
PROBLEM: when I filter by QuarterYear, my % variance collapses. I no longer have the denominator, always rendering zero.
Do you know why this is happening?
Thanks,
Lisa
Hi Lisa
Try this
Make a field of quarter in your master calendar
ex- Ceil(month(D)/3 as Quarter
And use below expression
=Sum({$<Year={"$(=max(Year))"}, Quarter= {"$(=max(Quarter))"}>}SALES) /
Sum({$<Year={"$(=max(Year)-1)"}, Quarter= {"$(=max(Quarter))"}>}SALES)
Raman
Hi Lisa
Basically the way qlik works when you select QuarterYear Qlik will filter out all the rows for that Quarteryear and then Qlik applies the set analysis.As the data set no longer (from your question) contains any values with FPYTD=1 it returns 0
if you want to ignore the selection add QuarterYear= into your set analysis
Hope it helps
Hi Dilip,
Thanks for your input!
But the thing is, I don't want to ignore QuarterYear. If I select Q1-2018, I want the expression to compute the sales value for the first quarter in 2018, divided by the first quarter in 2017.
What do I need to do to make this happen?
Thanks!
Lisa
Hi Lisa
Try this
Make a field of quarter in your master calendar
ex- Ceil(month(D)/3 as Quarter
And use below expression
=Sum({$<Year={"$(=max(Year))"}, Quarter= {"$(=max(Quarter))"}>}SALES) /
Sum({$<Year={"$(=max(Year)-1)"}, Quarter= {"$(=max(Quarter))"}>}SALES)
Raman
Hi Raman!
So, you don't recommend using flags like CYTD?
I created the field Ceil(Month(D)/3 in my script, and now I have a filter Quarter that displays Q1,Q2,Q3,Q4. But the ideal would be to have the field QuarterYear as in Q1-2018 throughout my app, so I don't lose context of the year.
Is this incompatible with using flags like CYTD?
Thanks!
Lisa
Flags are a good practice but if you want it to react to year selections you will need to do somethings like raman mentioned.
Alternatively in your original you can design the app to just select quarter instead of quarter year
Hi Lisa
As dilip said using of flags is consider as a good practices but here you want your values based on selections so i suggested use of direct fields instead of Flags.
QuarterYear is a string value which can not be use to compare values in set analysis. so i used separate quarter filed.
Raman
Hi raman.rastogi and hi dilipranjith!
My master calendar is based on flags... Mostly all of it.
Under which circumstances is then best to use flags? Because it's normal to expect my KPIs reacting to filter selections.
Thanks!
Lisa
Hi Lisa
Using flags (in the setanalysis) will work
- if always comparing current year to previous (any dimension quarter or daily).
- but this will be affected if you select a filter with year there. like i mentioned in my first reply
- typically you would ignore those filters in the set analysis e.g. Quarter Year, year etc. but other filters like quarted
If you need get the chart to be truly dynamic i.e. if you choose a year in the filter and chart will show that year and prior year. you will need to use variables. something like below
e.g. comparing between selected date and revious 6 months
declare 2 variables
vTestDate - =if(GetSelectedCount(OrderDate)=1,OrderDate,0)
vTestDateMinus6 - =if(GetSelectedCount(OrderDate)=1,Date(AddMonths(OrderDate,-6)),0)
your expression
if(vTestDate=0, Sum( OrderRecordCounter),Sum( {<OrderDate={"<=$(=vTestDate) >=$(=vTestDateMinus6)"}>} OrderRecordCounter))
Hi lisa
* We generally use flags to reduce calculation time from front end to increase charts response time.
It is good to use when you have complex business logic.
Use of flags give you less flexibility
ex - As you made CYTD flag in this only current year will come under this flag and if you want to change your year based on selection you cant change your flag value on selection.
So in this case you have to write whole logic in your expressions.
Thanks
Raman