Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
valpassos
Creator III
Creator III

Date comparison

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

1 Solution

Accepted Solutions
raman_rastogi
Partner - Creator III
Partner - Creator III

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

View solution in original post

10 Replies
dplr-rn
Partner - Master III
Partner - Master III

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

valpassos
Creator III
Creator III
Author

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

raman_rastogi
Partner - Creator III
Partner - Creator III

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

valpassos
Creator III
Creator III
Author

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

dplr-rn
Partner - Master III
Partner - Master III

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

raman_rastogi
Partner - Creator III
Partner - Creator III

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

valpassos
Creator III
Creator III
Author

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

dplr-rn
Partner - Master III
Partner - Master III

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

raman_rastogi
Partner - Creator III
Partner - Creator III

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