topic Re: pivot table numbers in QlikView

pivot table numbers

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am facing issue. I have following tables: Date_Table & Account.

Both table has Eve_date column common. I am using Eve_Date ( Date Table) in dimension & Sum(Actual Efforts ) from Account table in pivot. It should show all the dates from Date Table. But it is not showing.

I have used supres 0 values. I have join both table with Left Keep, but still it is not showing expected output.

Please help.

Re: pivot table numbers

qlikview979 — Tue, 16 May 2017 05:56:40 GMT

Hi ,

Can you please share sample app

Re: pivot table numbers

Anil_Babu_Samineni — Tue, 16 May 2017 05:57:05 GMT

If you feel to combine tables then use Where Exists Key word

Or , Go and use Qualify and Unqualify statements then count only for that dates. If not, You may check Data model preview whether data produced or not

Re: pivot table numbers

Anonymous — Tue, 16 May 2017 05:58:45 GMT

You can send your qvw once. We can see how you are approaching.

Re: pivot table numbers

Tue, 16 May 2017 06:02:35 GMT

if I use where exist, it will combine the dates which are exist in both table which Qlikview does automatically when I use concat. I want all dates from Date Table weather it is exist or not exist in Account table.

Re: pivot table numbers

qlikview979 — Tue, 16 May 2017 06:06:33 GMT

may be

if you go for Concatenate it will give all dates

go for left join it will take all dates from first table ,matching dates from second table

please share the sample data

Re: pivot table numbers

Anil_Babu_Samineni — Tue, 16 May 2017 06:07:21 GMT

Then go for Qualify and Un-Qualify Statements

Re: pivot table numbers

tyagishaila — Tue, 16 May 2017 06:28:35 GMT

In Dimension Tab

Check Show All Values

Re: pivot table numbers

vinieme12 — Tue, 16 May 2017 10:14:01 GMT

uncheck Supress Zero values

"I have used supres 0 values"

Re: pivot table numbers

Wed, 17 May 2017 12:00:17 GMT

Hi All,

Thanks its solved but I have another issue.

I am using "NA" for 'Populate Missing value' in presentation tab. I want to format that values in pivot as a white text and fill. I mean if pivot cells has value "NA" then format it white background and font color.

Thanks'

Re: pivot table numbers

Anil_Babu_Samineni — Wed, 17 May 2017 12:04:35 GMT

Then, Instead of NA in presentation tab use Space to hide. Why you want to show in condition.

Re: pivot table numbers

Wed, 17 May 2017 13:42:28 GMT

I am using Dimension "city" and formula that =If(sum(Emp)>0, Green, Red). where there are some city that doesnot has emp, it shows null & applying red format. I want to treat NULL as white.

Re: pivot table numbers

Anil_Babu_Samineni — Wed, 17 May 2017 14:04:08 GMT

May be Application needed for this Issue

Try this for luck

=If(sum(Emp)<0, Red(), Green())

Re: pivot table numbers

Wed, 17 May 2017 17:36:46 GMT

Not working. Tried already.

It considered text what ever you put for null values even 0 is considering text. So < 0 is not working

Re: pivot table numbers

Anil_Babu_Samineni — Wed, 17 May 2017 17:38:35 GMT

Can you share the application please? That demonstrates the issue

Re: pivot table numbers

Thu, 18 May 2017 06:38:02 GMT

i am working on trial version now. so can not share any file.

but my concern is that I want to format NULL ( what ever we defined in show null values) in Pivot table. Formatting means background color and font color only.

Please guide me on this.. stuck from long time.

Thanks in Adv

Re: pivot table numbers

tyagishaila — Thu, 18 May 2017 07:21:51 GMT

Go to Expression Tab

Explore expression(click on + sign)

click on background color then write expression

If(field or expression whatever=NA, Yellow, red or green as per your requirement)