topic Re: Set analysis - calculation with dates in QlikView

Set analysis - calculation with dates

annanirvin — Wed, 25 Nov 2020 16:16:04 GMT

Hi all.

Can someone please help me with this calculation?

=Num(NumericCount (DISTINCT {1<Ä_slag={'AEF'}, Substatus={'Inkomna'}, Datum={"$(=Date(Max(Datum),'YYYY-MM-DD'))"}>}Ärendeid) / Sum({<Datum={"$(=Date(Max(Datum),'YYYY-MM-DD'))"}>}P_AEF), '# ###%')

NumericCount (DISTINCT {1<Ä_slag={'AEF'}, Substatus={'Inkomna'}, Datum={"$(=Date(Max(Datum),'YYYY-MM-DD'))"}>}Ärendeid) gives the correct answer as well as Sum({<Datum={"$(=Date(Max(Datum),'YYYY-MM-DD'))"}>}P_AEF) but not when I combine them. I suppose the problem has to do with the Datum={"$(=Date(Max(Datum),'YYYY-MM-DD'))"?

Best regards,

Anna

Re: Set analysis - calculation with dates

sunny_talwar — Sun, 26 Mar 2017 12:08:38 GMT

Where are you using this? Text box or a chart? What do you get individually and what do you get when you combine them?

Re: Set analysis - calculation with dates

sasiparupudi1 — Sun, 26 Mar 2017 13:28:26 GMT

If you remove Num function, do you get the desired results?

Re: Set analysis - calculation with dates

annanirvin — Sun, 26 Mar 2017 14:54:26 GMT

I use it as text in chart in a gauge chart.

NumericCount (DISTINCT {1<Ä_slag={'AEF'}, Substatus={'Inkomna'}, Datum={"$(=Date(Max(Datum),'YYYY-MM-DD'))"}>}Ärendeid) gives '1' and Sum({<Datum={"$(=Date(Max(Datum),'YYYY-MM-DD'))"}>}P_AEF) gives '4' when used individually and the result should be 25%, but as you can see in the right chart above it gives 26,49%. Any idé of why and how to solve this?

Re: Set analysis - calculation with dates

annanirvin — Sun, 26 Mar 2017 14:54:51 GMT

No, it doesn´t work.

Re: Set analysis - calculation with dates

Anil_Babu_Samineni — Sun, 26 Mar 2017 15:56:05 GMT

Would you share sample to look

NumericCount (DISTINCT {1<Ä_slag={'AEF'}, Substatus={'Inkomna'}, Datum={"$(=Date(Max(Datum),'YYYY-MM-DD'))"}>}Ärendeid)

This will return only numeric numbers of Ärendeid where Filters are applying with Constant value like even if your selection is happening on filter pane it won't react.

I didn't known if there is any error on this. Seems ok to me

Sum({<Datum={"$(=Date(Max(Datum),'YYYY-MM-DD'))"}>}P_AEF)

This is also works for you, Try like below

Num(NumericCount (DISTINCT {1<Ä_slag={'AEF'}, Substatus={'Inkomna'}, Datum={"$(=Date(Max(Datum),'YYYY-MM-DD'))"}>}Ärendeid)/Sum({<Datum={"$(=Date(Max(Datum),'YYYY-MM-DD'))"}>}P_AEF),'#,#0%')

Re: Set analysis - calculation with dates

annanirvin — Sun, 26 Mar 2017 16:30:44 GMT

Unfortunately I can´t share since my work is confidential.

Thank you, but your solution gives 26,50%. Still not right that is.

Re: Set analysis - calculation with dates

Anil_Babu_Samineni — Sun, 26 Mar 2017 16:35:41 GMT

I've tried this?

=Num(1/4,'#,#0%') -- It is giving 25% in KPI. Can you check from your end

Re: Set analysis - calculation with dates

sasiparupudi1 — Sun, 26 Mar 2017 16:39:45 GMT

I think there may be a rounding issue here.. your sum might be returning 3.77 andhence you see 26%..

Re: Set analysis - calculation with dates

annanirvin — Sun, 26 Mar 2017 17:19:29 GMT

That´s right but for some reason I doesn´t seem to get '1' and '4' in my combined calculation, only when I use them individually.

Re: Set analysis - calculation with dates

annanirvin — Sun, 26 Mar 2017 17:21:00 GMT

Thanks, but the sum is 4,00.

Re: Set analysis - calculation with dates

sunny_talwar — Sun, 26 Mar 2017 18:05:12 GMT

May be this thread can address you confidentiality concerns

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample