Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
tim_at_ford
New Contributor III

Set Analysis Addition

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.

6 Replies
tincholiver
Contributor II

Re: Set Analysis Addition

Can you share some data for help you?
Partner
Partner

Re: Set Analysis Addition

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

tincholiver
Contributor II

Re: Set Analysis Addition

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,

Re: Set Analysis Addition

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')
tim_at_ford
New Contributor III

Re: Set Analysis Addition

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?

 

 

tim_at_ford
New Contributor III

Re: Set Analysis Addition

Sunny

Never mind!! I figured out the 01/01/Year or Year selected issue.

Tim