How to loop 6 months in pivot table based on application

[Anonymous]

Hi all

i am using pivot table for getting the no.of users for each application

i have nearly 35 application with 3 particulars for each application i have to show no.of users for last 6 months

some thing like

application name     particulars    12/2/2016  11/2/2016 10/2/2016 ..............

                         no.of users                 10              15           6

Wisdom            total no.of users          100         120             40

                          training requests         0           1                     5

.

.

.

30 applications

so i taken application name and particulars as dimension and

expression if(application='Wisdom' and Particular='no.of users' ,count({<Date={"$(vToday)"}>}userid))     for today(3 if statements for each application  1.no.of users,2.total no.of users ,3. training requests)

                                                                                                                               $(vYesturday)                  for yesterday and so on

                                                                                                                                $(vPrevious2)                  for day before yesturday  

writing this expression(total 180 expressions) for 6 months is very difficult  so is their any way to loop it for 6 months

and also how to sort  applications based on user selection(a to z or z to a)

please help me

regards,

mahesh

[marcus_sommer]

It's not quite clear to me what do you want to do. If you used a date-dimension - maybe restricted with: if(date >= today() -180, date) - you could simply use a single expression with a count() and without set analysis.

- Marcus

[Gysbert_Wassenaar]

Like Marcus_Sommer I don't see why you can't use dimensions and just three expressions. Can you explain the problem with that?

[Anonymous]

Hi Marcus

i am attaching qvw and xl which i need like that

i tried with conditional available in expression tab still it is showing like attached

plz help me

regards,

Mahesh

[marcus_sommer]

For me it's not clear what should be the result - qvw and xls are identical.

- Marcus

[Anonymous]

In qvw for Application IPR it is showing both ipr count and bgcg count and also for Application BGCG it is showing both ipr count and bgcg count i want to have ipr count only for application ipr and bgcg count only for application bgcg

in the above xl for bgcg s.no is 2

regards,

mahesh

[Anonymous]

hi wassenaar thanks for your response

please go through the above attachments

[jagan]

Hi,

Not sure what you are trying to do?  When I check your data model there is join/relation between Application and other tables, there should be some relation.  Can you attach your raw data and your expected output, so that it is easier to understand and help you.

Regards,

jagan.

[Anonymous]

Hi ,

thanks for your reply i attached the qvw and expected output in excel please go through that

In qvw for Application IPR it is showing both ipr count and bgcg count and also for Application BGCG it is showing both ipr count and bgcg count i want to have ipr count only for application ipr and bgcg count only for application bgcg

in the above xl for bgcg s.no is 2

[marcus_sommer]

Then you need only one expression with an appropriate condition like:

If(Application = 'IPR', count(IPR_ID), Count(BGCG_ID))

See also the attachment.

- Marcus