topic Re: With and without year selection counts are mismatching in QlikView

With and without year selection counts are mismatching

agastya — Mon, 10 Aug 2020 14:52:34 GMT

Hello Team,

Hope you all doing great!!!!!

Dimension :- If(Year="2020",Year)

Expression :-

Count(aggr( if((MaxString({<NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number)-MinString({1<NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number))>0,
if((MaxString({<NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number))>(MinString({<NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number)),
(MaxString({<NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number)-MinString({<NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number)),'')),Drawing_Roo_Program_Key))

If i am making year selection i.e 2020 in this case i am getting current count as "349"

when i am deselecting the Year selection count are mismatching i.e "103"

Thank you looking for positive reply:)

@sunny_talwar @bharathadde

Re: With and without year selection counts are mismatching

sunny_talwar — Mon, 10 Aug 2020 15:06:28 GMT

May be try this

Count({<Year = {'2020'}>}Aggr( If(MaxString({<NewFlag_Ignore = {'N'}, Year = {'2020'}>} DRAWING_REVISION_Number) - MinString({1<NewFlag_Ignore = {'N'}, Year = {'2020'}>} DRAWING_REVISION_Number) > 0, If(MaxString({<NewFlag_Ignore = {'N'}, Year = {'2020'}>} DRAWING_REVISION_Number) > MinString({<NewFlag_Ignore={'N'}, Year = {'2020'}>} DRAWING_REVISION_Number), MaxString({<NewFlag_Ignore = {'N'}, Year = {'2020'}>} DRAWING_REVISION_Number) - MinString({<NewFlag_Ignore = {'N'}, Year = {'2020'}>} DRAWING_REVISION_Number), '')) , Drawing_Roo_Program_Key))

But it seems like this could be converted to this

Sum({<Year = {'2020'}>}Aggr( RangeMax(MaxString({<NewFlag_Ignore = {'N'}, Year = {'2020'}>} DRAWING_REVISION_Number) - MinString({<NewFlag_Ignore = {'N'}, Year = {'2020'}>} DRAWING_REVISION_Number), 0) , Drawing_Roo_Program_Key))

Re: With and without year selection counts are mismatching

agastya — Mon, 10 Aug 2020 15:39:48 GMT

Used above range max formula "103" count written with/Without selection year filter @sunny_talwar

Re: With and without year selection counts are mismatching

agastya — Mon, 10 Aug 2020 15:42:31 GMT

349 value will reflect with/Without selection

Re: With and without year selection counts are mismatching

sunny_talwar — Mon, 10 Aug 2020 17:10:41 GMT

How about this?

Count({<Year = {'2020'}>}Aggr(

If(MaxString({<NewFlag_Ignore = {'N'}, Year = {'2020'}>} DRAWING_REVISION_Number) - MinString({1<NewFlag_Ignore = {'N'}, Year = {'2020'}>} DRAWING_REVISION_Number) > 0,

If(MaxString({<NewFlag_Ignore = {'N'}, Year = {'2020'}>} DRAWING_REVISION_Number) > MinString({<NewFlag_Ignore={'N'}, Year = {'2020'}>} DRAWING_REVISION_Number),

MaxString({<NewFlag_Ignore = {'N'}, Year = {'2020'}>} DRAWING_REVISION_Number) - MinString({<NewFlag_Ignore = {'N'}, Year = {'2020'}>} DRAWING_REVISION_Number), ''))

, Drawing_Roo_Program_Key))

Re: With and without year selection counts are mismatching

agastya — Tue, 11 Aug 2020 06:32:08 GMT

Hello ,

I tired both the expression unfortunately no luck 😞

@Sunny

Re: With and without year selection counts are mismatching

agastya — Tue, 11 Aug 2020 08:34:19 GMT

Issue is that

(Count({1<Drawing_Roo_Program_Key=p(Drawing_Roo_Program_Key)>} aggr(if(aggr(MaxString({< NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number)-MinString({1< NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number),Drawing_Roo_Program_Key)>0,
if((MaxString({< NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number))>(MinString({< NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number)),
(MaxString({< NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number)-MinString({< NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number)),'')),Drawing_Roo_Program_Key)))

When i am selecting 2020 year filter some of the record which is in 2019,2028

expected o/p

Re: With and without year selection counts are mismatching

sunny_talwar — Tue, 11 Aug 2020 10:01:24 GMT

Some of the records are from 2018 and 2019? But you still want to see them against 2020? Can you elaborate on why are you doing this?

Re: With and without year selection counts are mismatching

agastya — Tue, 11 Aug 2020 11:47:13 GMT

In the above image selected year is 2020 but couple of Drawing_root_key which is active in 2018 and 2019 as well .

so when i am selecting 2020 year all possible combination will going to consider in 2020

Re: With and without year selection counts are mismatching

sunny_talwar — Tue, 11 Aug 2020 12:24:04 GMT

Okay so that make sense, I can get you the result when 2020 is selected, but what happens when 2020 is not selected? How would the result look then? Or will you always display this for the most recent year?

Re: With and without year selection counts are mismatching

agastya — Tue, 11 Aug 2020 12:29:03 GMT

When 2020 is not selected still it will show 349 count only

something like this(sample)

Re: With and without year selection counts are mismatching

sunny_talwar — Tue, 11 Aug 2020 12:33:07 GMT

Would it be possible for you to share a sample?

Re: With and without year selection counts are mismatching

agastya — Tue, 11 Aug 2020 12:43:11 GMT

=Count({aggr(
if((MaxString({<NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number)-MinString({<NewFlag_Ignore={'N'},Created_Year=>}DRAWING_REVISION_Number))>0,
if((MaxString({<NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number))>(MinString({<NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number)),
(MaxString({<NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number)-MinString({<NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number)),'')),Drawing_Roo_Program_Key))

Using Above expression

Above expression giving correct o/p with selection without other possible years data not including in the Selected year (2020)

Re: With and without year selection counts are mismatching

sunny_talwar — Tue, 11 Aug 2020 12:51:27 GMT

okay, but you still need help with without selection, correct? Can you share a sample so that we can help you with that?

Re: With and without year selection counts are mismatching

agastya — Tue, 11 Aug 2020 12:54:47 GMT

Sunny i can't share any sample customer confidential

Can you please help me out to modified the expression /how can i include possible value which is reflecting in other years.

Re: With and without year selection counts are mismatching

sunny_talwar — Tue, 11 Aug 2020 12:57:48 GMT

I am not asking to share confidential data. All I need is a sample where I can see the issue... and this can be done in mocked up sample where the data is all made up data.

I am not sure I understand what you have which is why I am having issues in giving you an expression which works.

Re: With and without year selection counts are mismatching

agastya — Tue, 11 Aug 2020 13:51:06 GMT

Final expression :-=

=Count({1}aggr(
if((MaxString({<NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number)-MinString({1<NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number))>0,
if((MaxString({<NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number))>(MinString({<NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number)),
(MaxString({<NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number)-MinString({<NewFlag_Ignore={'N'}>}DRAWING_REVISION_Number)),'')),Drawing_Roo_Program_Key))

Re: With and without year selection counts are mismatching

sunny_talwar — Tue, 11 Aug 2020 14:07:32 GMT

This worked?

Re: With and without year selection counts are mismatching

agastya — Tue, 11 Aug 2020 14:59:48 GMT

Working for with selection not for without selection:(

Re: With and without year selection counts are mismatching

sunny_talwar — Tue, 11 Aug 2020 18:21:37 GMT

okay, I am not sure how to help with just looking at the expression. May be someone else can