Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am facing little weird behavior in qliksense w.r.t qlikview expression.
Let me explain in detail
1: My Source data
Year | Sales |
2017 | 200 |
2016 | 100 |
2015 | 300 |
2014 | 120 |
2013 | 300 |
2012 | 400 |
2011 | 234 |
2 :
my load script is like below in qliksense (similar in qlikview)
[Sheet2]:
LOAD [Year],
[Sales],
Right(Year,2)-1&'-'&Right(Year,2) as Fin_Year
FROM [lib://Desktop/Book1.xlsx]
(ooxml, embedded labels, table is Sheet2);
3:
For PY YTD my set analysis expression is
=Sum({<Year={"$(=max(Year)-1)"}>}Sales)
4.1 : In QlikView
When User Select value from Fin_Year It is showing correct PY YTD
For Example : If User select 16-17, Text object return 100
Working as expected
When User select value from Year from Year field then it is showing correct PY YTD
For Example : If User select 2017, Text object return 100
Working as expected
4.2 : QlikSense
When User select value from Year from Year field then it is showing correct PY YTD
For Example : If User select 2017, Text object return 100
Working as expected
When User Select value from Fin_Year It is showing 0
For Example : If User select 16-17, Text object return 0
Not Working as expected.
Can some one help me to understand this behavior.
gwassenaar, swuehl, mbaeyens, kaushik.solanki
Regards,
If you filter on Fin_Year in QlikSense, what values are possible in Year field?
What does a KPI object with Expression
=Max(Year)
resp.
=Max(Year) -1
show?
Is your Year field maybe one of the Sense automatically created date fields (and expect a Qlik date number instead year integer?) Check by using a KPI
=Num(Max(Year))
Hi Stefan,
Thanks for reply, Please find below screen shot for your reference.
Try
=Sum({<Year={'$(=Num(max(Year)-1))'}>}Sales)
Besides this, I assume that you have a year 2016 in your data (just can't tell from the screenshot).
edit: also use single quotes
Yes I already tried it.
with single quote , with double quote.
not working with any quote.
data is present for all year see below screen shot
Regards,
I also think that you Need to clear the active selection in Fin_Year if you want to modify Year to the previous year.
But this should apply to both QlikView and QlikSense.
=Sum({<Year={'$(=Max(Year)-1)'}, Fin_Year= >} Sales)
In your screenshot, how comes it shows two list boxes for Year, differently formatted?
How does the sense model look like?
After posting question, I am trying out few more things.
2nd Year is calculated List box, which I have created after posting the question to check what happen If created calculated list box.
Your earlier expression work i.e.
=Sum({<Year={'$(=Max(Year)-1)'}, Fin_Year=>} Sales)
But in QlikView I don't required to nullify Fin_Year field.
In QlikView This expression =Sum({<Year={'$(=Max(Year)-1)'}>} Sales) working properly.
Another thought what if I am using Fin_Year as calculated list box, then nullify won't work.
Regards,
It all depends on your real setting.
And I believe that you also need to clarify the field in QlikView, unless your data model is different from QlikSense or you are using e.g. a set identifier 1.