topic Re: Set Analysis in QlikView

Set Analysis

Zaga_69 — Sat, 16 Nov 2024 04:54:40 GMT

Hi everyone!

I’m using the following formula to calculate YTD and Previous YTD:

YTD

Sum({1 <DateKey={"$(='>=' & Date(YearStart(Max(DateKey)), 'YYYYMMDD') & '<=' & Date(Today(),'YYYYMMDD')"}>} [Product_Sales])

Previous YTD

Sum({1 <DateKey={"$(='>=' & Date(YearStart(Max(DateKey)), 'YYYYMMDD') & '<=' & Date(Today(),'YYYYMMDD')"}>} [Product_Sales])

The formulas above work, however it calculates the YTD and Previous YTD from a month perspective. I would like for example to calculate YTD from a day perspective (i.e. sales between 20180101 & 20181221)

I tried to replace the second part of the formula using:

date(today(),'YYYYMMDD')

however, it did not work.

YTD

Sum({1 <DateKey={"$(='>=' & Date(YearStart(Max(DateKey)), 'YYYYMMDD') & '<=' & Date(Today(),'YYYYMMDD')"}>} [Product_Sales])

Finally DateKey was created as follows:

date(Date#("0PSTNG_DATE",'YYYYMMDD'),'YYYYMMDD') AS DateKey,

Any idea will be much appreciated.

Best regards,

Edi

Re: Set Analysis

Anil_Babu_Samineni — Fri, 21 Dec 2018 11:12:37 GMT

If there is no concern from date field. You can try
Sum({1 <DateKey={">=$(=Date(YearStart(Max(DateKey)), 'YYYYMMDD')<=$(=Date(Today(),'YYYYMMDD'))"}>} [Product_Sales])

Re: Set Analysis

Zaga_69 — Fri, 21 Dec 2018 11:42:58 GMT

Thanks, but unfortunately it did not work 😕

Re: Set Analysis

Anil_Babu_Samineni — Fri, 21 Dec 2018 11:50:46 GMT

Can you please share sample data set or application. Can you check this in text object and tell us what it returns
Date(YearStart(Max(DateKey)), 'YYYYMMDD')

Re: Set Analysis

sunny_talwar — Fri, 21 Dec 2018 12:17:26 GMT

@Zaga_69 wrote:
The formulas above work, however it calculates the YTD and Previous YTD from a month perspective. I would like for example to calculate YTD from a day perspective (i.e. sales between 20180101 & 20181221)

Can you elaborate on this? What do you mean when you say that the YTD and Previous YTD from a month perspective? Can you explain this with an example?

Re: Set Analysis

Zaga_69 — Fri, 21 Dec 2018 12:26:11 GMT

This formula

Date(YearStart(Max(DateKey)), 'YYYYMMDD')

brings the following result:

20180101

Re: Set Analysis

Anil_Babu_Samineni — Fri, 21 Dec 2018 12:30:40 GMT

Try this, make sure you are not looking the result after selection because you are ignoring all selections here. Check validation of expression as well

Sum({1 <DateKey={">=$(=Date(YearStart(Max(DateKey)), 'YYYYMMDD')<=$(=Date(Max(DateKey),'YYYYMMDD'))"}>} [Product_Sales])

Re: Set Analysis

Zaga_69 — Fri, 21 Dec 2018 12:34:23 GMT

Month perspective:

when using the formulas posted previously, they calculates for example YTD 2017 (i.e. Previous YTD) taking into account the whole December. I would like to be able to compare the same period, for example:

YTD (20180101 & 20181221)

Previous YTD (20170101 & 20171221)

Instead of that the formula calculates for the Previous Year:

Previous YTD (20170101 & 20171231)

And it is because of this part: Date(addyears(Max(DateKey),-1), 'YYYYMMDD') which brings 20171231...

I tried to replace it by date(addyears(date(today(),'YYYYMMDD'),-1),'YYYYMMDD'), however it did not work

Re: Set Analysis

sunny_talwar — Fri, 21 Dec 2018 12:40:56 GMT

So when you use this in a text box object...

=date(addyears(date(today(),'YYYYMMDD'),-1),'YYYYMMDD')

you don't see this?

Can you show us the screenshot of what you see when you use the above in a text box object?

Re: Set Analysis

Zaga_69 — Fri, 21 Dec 2018 12:47:24 GMT

It brings the right date:

Re: Set Analysis

Zaga_69 — Fri, 21 Dec 2018 12:56:32 GMT

back to my original question, I would like to replace red part of this formula:

=Sum({1 <DateKey={"$(='>=' & Date(YearStart(Max(DateKey)), 'YYYYMMDD') & '<=' & Date((Max(DateKey)), 'YYYYMMDD'))"}>} [Product_Sales])

by this one:

date(addyears(date(today(),'YYYYMMDD'),-1),'YYYYMMDD')

However when replacing it does not work....

Re: Set Analysis

sunny_talwar — Fri, 21 Dec 2018 12:56:35 GMT

So, that works, but you think that this doesn't?

Sum({1<DateKey = {"$(='>=' & Date(YearStart(Max(DateKey), -1), 'YYYYMMDD') & '<=' & Date(AddYears(Today(), -1), 'YYYYMMDD'))"}>} [Product_Sales])

Can you try the above expression in a chart where you have DateKey as a dimension and share a screenshot

Re: Set Analysis

sunny_talwar — Fri, 21 Dec 2018 12:57:52 GMT

Try this expression and I would like you to share a screenshot where I can see 20181222, 20181223, 20181224, etc in a chart where you use the expression with DateKey as dimension

Sum({1<DateKey = {"$(='>=' & Date(YearStart(Max(DateKey), -1), 'YYYYMMDD') & '<=' & Date(AddYears(Today(), -1), 'YYYYMMDD'))"}>} [Product_Sales])

Re: Set Analysis

Zaga_69 — Fri, 21 Dec 2018 13:07:24 GMT

It works!

Thanks, I really appreciate your effort and help!

Re: Set Analysis

sunny_talwar — Fri, 21 Dec 2018 13:09:27 GMT

So, all good then?