Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
To whoever can help me!!
I want to do a YOY analysis for my KPI. I have five years of data. I want to subtract out the counts of 01/01/2018 thru 08/29/2018 against 01/01/2017 thru 08/28/2017, a YOY.
Now I know this algorithm is wrong (being new to Qlik View Set Analysis)
=Num(Count({<Salesdate={"<=$(vCurrentDate)"}>}indictor) - count({<Salesdate={"<=01/01/2018"}>}indicator)
- Count({<Salesdate={"<=$(vPrevDate)"}>}indicator) - count({<Salesdate={"<=01/01/2017"}>}indicator), '###,##0')
However, individual everything for each year works fine! I get my 2018 and 2017 counts individuals so I know I'm doing my set analysis correctly.
Where I run into issues is how to perform an arithmetic operation on both sets. I know I'm supposed to get a negative number. My first set has 900 records (2018) and my second set has 2,300 (2017). I should get a negative number!!
Am I using the correct functions? I have review a lot of material and it all appears to be just comparing year to year not YOY.
Think you may have made a small mistake; you used '<= 01/01/2017' rather than >=
=Num(Count({<Salesdate={"<=$(vCurrentDate)"}>}indictor) - count({<Salesdate={">=01/01/2018"}>}indicator)
- Count({<Salesdate={"<=$(vPrevDate)"}>}indicator) - count({<Salesdate={">=01/01/2017"}>}indicator), '###,##0')
You should also put dates from up to each count, if you use Count ({<Salesdate = {"<= $ (vCurrentDate)"}>} indictor) this account will bring data from the previous year which you do not need.
you should express something like the following
= Num (
(Count ({<Salesdate = {"> = 01/01/2018 <= $ (vCurrentDate)"}>} indictor)
-
count ({<Salesdate = {"> = 01/01/2018 <= $ ( vCurrentDate) "}>} indicator))
-
(Count ({<Salesdate = {"> = 01/01/2017 <= $ (vPrevDate)"}>} indicator)
-
count ({<Salesdate = {"> = 01/01/2017 <= $ (vPrevDate) "}>} indicator))
, '###, ## 0')
that way I will only tell you the data contained in that range,
What exactly goes wrong when you use the combined expression? See an incorrect number? See null? If you see null, you can try this
=Num( RangeSum( Count({<Salesdate={"<=$(vCurrentDate)"}>}indictor), -Count({<Salesdate={"<=01/01/2018"}>}indicator), -Count({<Salesdate={"<=$(vPrevDate)"}>}indicator) -Count({<Salesdate={"<=01/01/2017"}>}indicator) ) , '###,##0')
Or may be this (changed the sign for the last count from - to +
=Num( RangeSum( Count({<Salesdate={"<=$(vCurrentDate)"}>}indictor), -Count({<Salesdate={"<=01/01/2018"}>}indicator), -Count({<Salesdate={"<=$(vPrevDate)"}>}indicator) +Count({<Salesdate={"<=01/01/2017"}>}indicator) ) , '###,##0')
Sunny
This issue has been resolved for arithmetic of the sets. However, I've got another one now involving the two hard coded January dates, which I need, 01/01/2018 and 01/01/2017!!
In my set analysis statement for either {<Salesdate={"<=01/01/2017"}>}
I want to make the 01/01 a constant and then the 2017 variable.
For example:
My current date processing is such:
08/29/2018 thru 01/01/2018
vs.
08/29/2017 thru 01/01/2017
Now I want to make both 01/01 (MM/DD) constant and the year variable, conceptual example
{<Salesdate={"<=(variable one with 01/01)(variable two 2017 or whatever year I am comparing to)"}>}
How do I do that?
Sunny
Never mind!! I figured out the 01/01/Year or Year selected issue.
Tim